Here’s an Excel tip on the LEN function.

Generally speaking, LEN returns the amount of characters that a cell displays.

However, lets look at a few examples and exceptions:

1. Normal text:

Here, the text “Nick” has 4 characters, so LEN returns 4. Nothing complicated there

2. Date Formula:

We have entered =TODAY() as the date formula.

The LEN function returns 5. But “16-Feb-09” has 9 characters I hear you say.

What’s happening ?

Well, for dates, Excel actually stores them as numbers so “16-Feb-09” is actually 39860, and the LEN of that is 5.

3. NOW() formatted as a date:

Here, the NOW() function is used, and LEN returns 16. This again reflects how NOW() is represented in Excel. NOW() is also actually a number: 39860.4762944444, and despite being formatted as a date, the internal representation remains the same.

4. £4,000.00

Excel looks at this as a number, and returns 4 – the 4 digits of 4000.

5. “ £699.00” 

Here, I have pasted a number from my internet bank statement. Note, LEN is returning 8 when you might expect 3. The trouble here is that when you copy and paste data from the web, it often contains extra characters. These need to be removed for Excel to understand the numbers. Here, the LEN function thinks “ £699.00” is a string, and counts all the characters including the one at the beginning. NOTE: LEN can be used to quickly analyse data to eyeball if there are any problems with it like extra spaces or odd characters as it will return a larger number than expected.

6. “=Nick”

“=Nick” returns “Nick”, and that has 4 characters.

7. “=1/0” - an error

Errors are not handled well with the LEN, and it just returns the same error.

8. ="Nick"="Excel Expert"

Well, unfortunately for me, Excel returns FALSE... this has 5 characters.

Download a spreadsheet to practice the LEN function in Excel

Training Video - LEN function