How to count length of text in Excel

Have you ever wondered if there is a function in Excel similar to the word count function of Microsoft Word? Unfortunately, Excel does not have a function that can do a word count, however, it does have a function to count the number of characters in a text residing in a cell. In this article, I will be covering this function called LEN in Excel.

How LEN function works in Excel?

The LEN function evaluates a text argument and returns the number of characters in that text argument being supplied. 

The function is written as :

=LEN(text)

Say if you have a text cell that contains a person name "David", you can supply this string of text to the LEN function as an argument and the function will return the number of characters in this text string. In this case, the LEN function will return 5.

=LEN("David")    ~ return 5

If the text "David" resides in cell A2, you can also reference the function to cell A2, it will also return 5.

=LEN(A2)    ~ return 5

One thing worth noting is that the LEN function counts the number of characters in the text, including spaces and punctuation. You can think of it as a function that returns the length of a word or sentence in a cell. 

LEN function also counts the number of digits in the cell if it is a number.

Why does the LEN function not work in Excel at times?

There are leading and trailing spaces

If there are leading spaces before the text and trailing spaces after the text, the LEN function will give an incorrect result. 

Take the example below, the correct result should be 19 but instead the function return 23. That is because there are some spaces surrounding the word.

To work around this, you can first use the TRIM function to remove the leading and trailing spaces of the text before using the LEN function. 

The below diagram shows how the formula can be written to result in the correct number of characters counted. In this case, 19 is returned.

There are non-printable characters

Non-printable such as line breaks can cause the LEN function to factor them in during the counting.

To work around this, you can first use the CLEAN function to remove the non-printable, (line break) in the text before using the LEN function. 

The below diagram shows how the formula can be written to result in the correct number of characters counted.

You are working on a date in a text-formatted cell

If you try to use the LEN function on a text cell that contains a date, you may get the result that you weren't expecting. 

Take the diagram below as an example, you should expect the number of characters to be 9 including the backslash, instead the function return 5. 

Let me try to explain this part a little further. Excel evaluate date values as a number, starting from  1. The 1 represents the date 1/1/1900 so a current date like 18/7/2022 is equivalent to 44760. Thus, this is the reason why the LEN function threw out 5 as the number of characters in the text instead of 9.

To work around this, you can first use the VALUE function to convert the data into date format, follow by the TEXT function to convert it into text, and thereafter, you can wrap both functions with the LEN function. 

This way, you can get the LEN function to return the correct number of characters in the text as shown in the diagram below.

You expected the formatting to be counted

The LEN function does not count the formatting of a dollar amount. For example, if the cell has $1000.00 in it, the function will only take into account the number 1000, ignoring the dollar sign, the thousand separators, and decimals.

If your intention is to count all the values in the cell including formatting of the amount, you can write the formula as shown below.

When to use the LEN function in Excel?

LEN function can be used as a standalone function in cases when you want to count the number of characters in a word or sentence in a cell. 

In some other cases, it can be passed as an argument into other functions such as the LEFT, MID and RIGHT functions in Excel.

Take the example below, say if you want to extract only the month into cell C2, you can include the LEN function as an argument pass in the LEFT function.

The LEFT function takes in two parameters, the first argument takes in the text that you would like it to evaluate, and the second argument takes in the number of characters you would like the function to return.

You can first use the :

1. LEN function to find the length of the text;

2. Subtract 5 (this is because the year 2022 together with a space character makes up 5 characters);

3. Include the above two steps as an argument in the LEFT function

The formula can be written as shown in the diagram below. The LEFT function together with the LEN function will give you the partial text "JULY".

Conclusion

By using this LEN function, you will never ever need to count the number of characters in a text string one by one again.

Do check out the video at the top of this article if you would like to see in action what I have shared.