In this article, we will learn how to count the length of text or the number of characters in Excel.

What is the Excel Length formula? 

The LEN formula in Excel is used to determine the length of a particular string in excel in the form of the number of characters. The number of characters can be calculated with or without spaces using the LEN formula as per requirement. 

Let us look at how to calculate the number of characters using the length formula in Excel. 

Using the LEN function in Excel 

Let’s say you want to want to determine the number of characters in the word “Process”. Here’s how to use it: 

1. Go to a cell and type “=LEN(“Process”)”. 
   

2. Hit Enter. This will show you the number of characters in the text “Process”.
   Note: The word “Process” can be replaced by the text in question for which the characters need to be determined.

Important Things To Note: 

– The LEN function in excel also takes into account the numeric values

– However, it ignores the formatting of the numbers.
So while =LEN(Zen Ten) will be 7 characters, =LEN($1,000) will only be 4. 

– It automatically takes into consideration the spaces between the text

– For an empty cell with no data, the Length is 0

Now, let us look at the various ways in which we can use the Excel length formula for text. 

Excel Length Formula For A Single Cell

To calculate characters of a text in a particular cell, say A1 which has the text “I love excel” 

1. Go to an empty cell say B1 and type =LEN(A1) 
   
2. Hit Enter. You’ll see the number of characters in cell B2.

Length Formula In Excel For Multiple Cells

To calculate the sum of the length of various cells, say A1, A2, A3 and A4.

1. Let’s say we have some text in cells A1 to A4.
   

2. Go to an empty cell say D1 and type =SUM(LEN(A1),LEN(A2),LEN(A3),LEN(A4))
   

3. Hit Enter. Cell D1 will give you the sum total of the number of characters in cells A1, A2, A3 and A4.
   

Remove leading and trailing spaces while using the Length function in Excel

To calculate the length of characters in a cell, say A1, without calculating the leading and trailing spaces we’ll combine excel length formula with trim formula.

1. When we use the Len function for cell A1, it’ll count the leading and trailing spaces as well.
   
2. To avoid counting spaces, we’ll use both Len & Trim. Go to cell B2 and type =LEN(TRIM(A1))
   

3. Hit Enter. You’ll see that the length excludes the leading and trailing spaces.
   

Remove all spaces while using the LEN function in Excel

To calculate the sum of the length of characters without calculating any spaces in between the text we’ll use length function with the substitute function.

1. Cell A1 has a text which has multiple spaces in between the words.
   

2. Just go to an empty cell C1 and type =LEN(SUBSTITUTE(A1,” “,””))
   

3. Hit Enter. This combination of SUBSTITUTE and LEN formulas in Excel will give you the length without counting any spaces. 
   

Other important formulas 

If you enjoyed learning about this formula, here are some other important Excel tricks & tips: 

1. Calculate interquartile range in excel
2. Create a bell curve in excel
3. Create data entry form in excel

Conclusion

This was all about the length formula in excel and its various uses. We hope that this helped you understand how you can use the Excel Len function to do character count in excel cells and in various other situations. 

If you have any doubts or suggestions for us, then leave a comment below and our team will get back to you.