Using LEN in Excel to get the total number of characters in a cell

If you need consistency in the length of text that you are entering, or if you want to make sure that what you are typing in a cell is within a certain number of characters then the LEN function can be used to easily count how many characters a cell has.

Download File

If you would like to follow along download the attachment below.

Using LEN in Excel to get the total number of characters in a cell.xlsx

Download XLSX • 19KB

What is the LEN function

The LEN function counts characters, spaces and punctuation in a cell.

The syntax for the function is simple and there is only one parameter:

=LEN(text)

• The text you want to count the number of characters in

How does the LEN function work

LEN works by counting every single character in the text, including:

• Letters (capital or lowercase)

• Numbers (e.g. "123")

• Spaces (e.g. "Hello World" has a space between "Hello" and "World")

• Special characters (e.g. punctuation like "." and "!" or symbols like "@")

  
  

LEN behaves differently depending on the values entered in cells.

LEN with Words

When there is a single word in a cell LEN will count the individual letters regardless of whether they are capital or not.

LEN with Words and Spaces

LEN treats spaces as characters so when there are words separated by spaces both the letters and spaces are counted.

LEN with trailing spaces

Spaces at the end of text can be hard to see, but as Excel treats spaces as characters if a text has a space at the end, it will be counted as part of the text.

LEN can be useful for seeing if a text has a trailing space as it will count it even if it is not immediately obvious that it is there.

The text in A2 has a space at the end, it is not possible to tell from looking at it but LEN still counts it.

If you do notice trailing spaces in your text you can use the TRIM function to remove them.

Click here to learn more about the TRIM function.

LEN with characters

Any other characters in a text value is also counted by LEN, including punctuation.

LEN with text and numbers

When there are text and numbers in a string LEN treats each individual number as a character and counts them along with the letters.

LEN with numbers only

If there are only numbers in a cell then LEN will count the total individual numbers, it will not sum them up.

LEN with blank values

If LEN is pointing to an empty cell with nothing in it the function will return 0.

Using LEN with IF statements

LEN can be nested in other functions.

Using LEN with an IF statement can be a useful way of making sure that you are warned if your text length has gone over a maximum limit.

An example of this may be accounting systems, where a description may be limited to a maximum of 30 characters, with anything above this length cut off.

Using LEN with IF would allow you to display a message if the length of text in a certain cell was too long.

=IF(LEN(A1)>30,"Too long","Ok")

Using Len with Conditional Formatting

If you want a cell to change colour if the length exceeds the maximum you want then you can use LEN with conditional formatting to achieve this.

In the Home tab go to Conditional Formatting and select New Rule

In the New Formatting Rule window select Use a formula to determine which cells to format and in the formula bar enter the formula:

=LEN(A2)>30

This formula assumes there is text in cell A2 and you want it to change colour if that text is greater than 30 characters

To choose the colour you want your cell to change to if the text is greater than the value chosen in the formula entered click on Format...

This will open the Format Cells window, go to the Fill tab and choose the colour you want and then click OK.

In this example red has been chose.

Once you have clicked OK you will be taken back to the New Formatting Rule window, click OK again to return to the worksheet.

The cell will now show as red when it has text greater than 30 characters in it. When you enter a text less than 30 characters it will automatically change back to white.

Conclusion

The LEN function is a simple way to count the total number of characters in a cell, including letters, numbers, spaces, and punctuation.

It can help ensure text consistency, detect trailing spaces, and manage character limits in data entry.

Nesting it in other functions, like IF statements, or using it with conditional formatting can help highlight cells that contain more characters that you want to be entered.