LEN Formula in Excel(Table of Contents)
LEN Formula in Excel
LEN function categorized under text functions in excel. LEN is used to count the number of characters in a text or string. It is the length function used to calculate the length of a string which contains special characters, space, numbers, letters, etc. This will count the characters in a string including space or special characters. This always returns a number as output. The string can be directly mentioned in the function by expressing the string within a double quotation or refereeing a cell. If the cell contains the text or string which you want to find the count of characters, then cell address can be used with the LEN function.
The LEN indicates the short form of length. This is text function returns the number of characters included in a string. The LEN function works as a count function to count the characters in a text. This will count the space, numbers, special characters, characters, etc.
Function for LEN
LEN function returns the number of characters in a text string.
LEN Formula:
- The text is a mandatory argument.
- The text can be the string you want to count the number of characters or the cell reference which consists of the text string you want to count.
How to Use LEN Formula in Excel?
LEN Formula in Excel is very simple and easy. Let’s understand how to use the LEN Formula in Excel with some examples.
Example #1
In the given example let’s find out the length of text given the column “Name of companies”.
To get the length of each name to apply the formula =LEN(A2) in the next column.
Below is the Result.
Drag the same formula for the rest of the cells the length for each string is produced and it returns the length as a number of characters included.
Example #2
Below is the example of how the length is calculated in a text with different characters.
The length is detected as 2 since space exists before the character given.
The Text consists of only two characters and the Length is returned as 2.
The Text included a special character and is counted along with the text.
While checking you can see there found only two characters can be seen in the cell, but the Length is detected as 15. This happened because of a couple of lading space and space between the numbers.
Apply the Formula in cell C2.
Similarly, For Cell C3.
The Length for each text is shown and in the first text, a special character is included and is counted with the length formula.
The LEN Formula does not count formatting Length, here the count of 100 only calculated.
Example #3
You can directly give the string to the LEN function, where the string is mentioned in the double quotation.
=LEN(“This is len function”)
The result will be as given below.
Example #4
How to count the characters excluding the extra spaces?
We have the details of the order numbers. And when we apply the Excel LEN formula it returned the number of characters. But it included the unwanted spaces between the numbers.
Apply the formula in Cell B2.
The result will be as given below.
Drag the Cell B2. The number of characters returned. The numbers included the space between numbers so the length is not unique.
To get the number of characters after avoiding these extra spaces let’s use the substitute function along with the Excel LEN Formula. Select the Cell C2 and apply the formula =LEN(SUBSTITUTE(A2,” “,””))
The result will be as given below.
Drag the formula to the rest of the cell. The substitute function will replace the space between the numbers. SUBSTITUTE(A2,” “,””)) and then operate with the LEN formula. The function ‘SUBSTITUTE(A2,” ”,””)’ will replace the spaces with null. In the formula will work as string or text given in C2, space will be replaced by null in C2.
And the formula LEN (SUBSTITUTE(A2,” “,””)) will find the number of characters in C2 after replacing the space. When the unwanted spaces are removed the number of characters returned the same value.
The difference in a number of characters can be easily understood from the below column, the length is the length without removing unwanted spaces and the column Without space indicates the number of characters after removing the extra spaces.
Things to Remember About LEN Formula in Excel
- The LEN function will always return a number.
- LEN function can be used by combining with other functions in Excel.
- The trim function can be used with the LEN function to return the number of characters after removing the leading and trailing spaces.
- LEN will accept the cell reference or the text directly as an argument and return the number of characters.
- LEN function calculates the number of characters including spaces, special characters, numbers, and text.
- If the cell is empty, the LEN function returns ‘0’.
- LEN does not count the formatting length.
Recommended Articles
This has been a guide to LEN Formula in Excel. Here we discuss How to use LEN Formula in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
