Many a time, while working in excel an excel user may face a challenge about how to calculate or count the total number of characters/letters in a particular cell or text in excel. This challenge is easy to overcome if you know how to use the LEN Function in excel.
In this tutorial, we would learn about the LEN formula in excel, its syntax, arguments with examples.
Here we go 😎
When to Use LEN Function in Excel
The Excel LEN function is used to find the total number of characters in a particular text or string cell. In other words, it is a useful function to calculate the total length of a text string or a cell.
This function is not limited to text string length. The LEN formula is also capable to count the length of a number (i.e. how many digits does a number contain). However, the output is in a text format.
This function is a part of the Text function group, and therefore it returns a text value.
Syntax and Arguments
The only argument of the LEN excel formula is explained in the below bullet point.
- text – In this argument, specify the text of which you want to return the number of characters.
The text argument can be either
- entered manually (within double quotes), or
- used as a cell reference, or
- a result and output of some other formula.
Examples of LEN Function in Excel
Let us now look at some examples to understand how does the LEN function works in excel.
Ex. 1 # Using Excel LEN Function For Counting Number of Characters
In this basic example, let’s look at working of the excel LEN formula.
To calculate the length of the above listed words in excel, simply use the following formula in cell B2 and drag it to other cells.
As a result, excel would return the letter count.
From the above example, following inferences can be brought out:
- Excel also counts a blank character (or a space character) as 1 letter (for eg – Conditional Format).
- Excel also considers numeric characters while counting the number of characters using the LEN formula (for eg – 12 Excel Tips).
Ex. 2 # Counting Number of Digits in Excel Cell
As seen above the LEN formula is also capable to count the number digits in a numeric value.
Let’s look at the following numeric values in column A of an excel worksheet.
To calculate the number of digits count, simply use the following formula in cell B2, and drag it to other cells:
It is important to note that the LEN function counts the actual number of digits in a number, irrespective of the cell’s number formatting.
In the above example, look at the value in cell A2 (viz. 15427.000). Also, there are 9 digits including decimal point, the LEN formula returns the count of the integer part (i.e. 15427), by ignoring the cell formatting.
Bonus Trick – Counting Number of Words in Cell in Excel
Now, when you are aware about working of LEN function with basic example. Let us now move ahead and learn how to use the LEN function to do complex excel tasks.
Although LEN function counts the total number of characters in a cell, there is no specific formula or function to count the number of words in a cell in excel.
Surprisingly, you can use the combination of the FIND and the SUBSTITUTE function to achieve this.
Look at the below example:
As a result, excel would return 6 as the output.
With this way we can find the number of words in a sentence in excel.
Explanation – The LEN(A2) portion finds the count of characters in cell A2 including the space character. LEN(SUBSTITUTE(A2,” “,””)) counts the number of characters in cell A2 by substituting space with nothing. It means LEN(SUBSTITUTE(A2,” “,””)) counts the number of characters in cell A2 excluding space. Finally, 1 is added to their value.
With this we have completed this blog on how to use the LEN function with some classic example.
Thank You 🙂