How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps
Home >> Excel Tutorials from Compute Expert >> Excel Formulas List >> How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps
In this tutorial, you will learn completely the use of the LEN formula in excel. This formula is useful if you want to know how many characters are there in your data.
Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more
Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!
Table of Contents:
- LEN usefulness
- LEN result
- Excel version in which LEN can start to be used
- The way to write it and its input
- Example of usage and result
- Writing steps
- “Clean” text before we count its number of characters: LEN TRIM
- Count the number of characters in a cell range: SUMPRODUCT LEN
- Count the number of a specific character in data: LEN SUBSTITUTE
- Count the number of a specific character in a cell range: SUMPRODUCT LEN SUBSTITUTE
- Count the number of characters in data without spaces: LEN SUBSTITUTE
- Exercise
- Additional note
LEN Usefulness
LEN can be used to count the number of characters in data/cell content.LEN Result
LEN will give you a number that represents the number of characters in the data you input into it.Excel Version in Which LEN Can Start to Be Used
LEN can start to be used since excel 2003.The Way to Write It and Its Input
Generally, here is the writing of a LEN formula in excel:
=LEN(text)
The only input LEN needs is text, which is the data you want to know the number of characters from.
Example of Usage and Result
The following will give and explain the usage and result example of the LEN formula in excel.
As you can see in the example, LEN will count all characters inside data without exception. These characters include spaces, numbers, letters, and other kinds of characters.
The LEN writing is pretty easy because you just need to input your data to LEN. Because of that, don’t hesitate to use it if you need this formula’s function!
Writing Steps
The following will give the steps to write LEN in excel. Each step will be explained with a screenshot to help you learn it easier.-
Type an equal sign ( = ) in the cell where you want to put the number of characters in your data
-
Type LEN (can be with large and small letters) and an open bracket sign after =
-
Input the data you want to count the number of characters of or the cell coordinate where the data is
-
Type a close bracket sign
- Press Enter
-
Done!
“Clean” Text Before We Count Its Number of Characters: LEN TRIM
Sometimes, a text we want to count the number of characters contains many redundant spaces (there are spaces in front or behind it or probably there is more than one space in between the text words). This can make our LEN result wrong as it also counts those redundant spaces to determine its result.So, how to make sure we get the number of characters from our text that doesn’t include those redundant spaces? The answer is by using the TRIM function before applying LEN in your text.
The way to write LEN and TRIM combination in excel is as follows.
=LEN(TRIM(text))
Pretty easy, isn’t it? You just need to input your text into TRIM before applying LEN to it. Your text characters counting process will certainly be correct because of that!
To make it clearer, here is the example of LEN on the text with redundant spaces, before and after using TRIM.
Before:
After:
You can see the difference, right? In the first screenshot, redundant spaces in front, behind, and in between the text words are still counted. Meanwhile, in the second screenshot, the redundant spaces have been removed using TRIM. TRIM makes the redundant spaces in the second screenshot not counted.
So, if you want to count characters in a standard text (without spaces in front or behind it and the number of spaces in between the words is only one), you should use this combination of TEXT and TRIM.
Count the Number of Characters in a Cell Range: SUMPRODUCT LEN
LEN usually only counts the number of characters in one data/cell content. What if you want to count the number of characters in a cell range?One thing you can do is using SUMPRODUCT together with LEN. The SUMPRODUCT function, which can process and sum numbers in a cell range, makes it an ideal solution for this.
In general, the combination of SUMPRODUCT and LEN is written in excel as follows.
=SUMPRODUCT(LEN(cell_range_contains_data))
As you can see, the writing isn’t too hard. You just need to input the cell range containing the data you want to count the number of characters from. Then, you input your LEN into SUMPRODUCT.
To make it clearer, here is an example of SUMPRODUCT and LEN combination application in excel.
SUMPRODUCT, which process and sum data in an array, enables LEN to count the number of characters in a cell range. This combination is important for you to know if this counting process is what you need in your data processing.
Count the Number of a Specific Character in Data: LEN SUBSTITUTE
LEN can help you to count the number of all characters in data. But, what if you just want to count the number of a specific character? Use SUBSTITUTE to help your LEN formula to count this.Generally, here is the writing of the two formulas combination to count the number of a specific character in data.
=LEN(data)-LEN(SUBSTITUTE(data,the_character_you_want_to_count,””))
The explanation of the process done by the writing above is as follows.
In the first writing part (before the - sign), you count the number of characters in your data using LEN normally. In the second part (after the - sign), you use LEN in your data that has been changed by SUBSTITUTE before.
The SUBSTITUTE helps to remove the specific character you want to count in your data (by substituting this character with blank data which is represented by two quotes sign (“”)). This makes you subtract the number of all characters in your data with the number of characters excluding your specific character. This subtraction process will give you the number of your preferred character in your data.
As an application example of the explanation above, you can see the screenshot below.
Pretty clear, isn’t it? In the above example, we can see how the counting process of the “e” letter in each data is done. Use the LEN and SUBSTITUTE combination with the correct writing to get the number of a specific character you need!
Count the Number of a Specific Character in a Cell Range: SUMPRODUCT LEN SUBSTITUTE
What if what you need is the number of a specific character in a cell range? For that, you need to combine three excel formulas: SUMPRODUCT, LEN, and SUBSTITUTE.Generally, here is how the combination of the three formulas is written in excel.
=SUMPRODUCT(LEN(cell_range)-LEN(SUBSTITUTE(cell_range,the_character_you_want_to_count,””)))
In the LEN and SUBSTITUTE writing given in the previous part, change the data input with your cell range. Then, surrounds this writing with SUMPRODUCT to get the counting result you need.
SUMPRODUCT function can help you to process numbers in an array in a cell range. This enables your LEN and SUBSTITUTE input to be a cell range. Thus, the counting process of the specific character can be done.
Below is the example of the SUMPRODUCT LEN SUBSTITUTE use to count the instances of a character in a cell range.
In the example above, we can see how the correct SUMPRODUCT LEN SUBSTITUTE writing can give the result we need. Follow the writing pattern when you need the counting function.
Count the Number of Characters in Data Without Spaces: LEN SUBSTITUTE
Sometimes, when counting the number of characters in a text, you want to exclude spaces in the process. This cannot be done if you only use LEN as LEN counts all the characters in your text, including spaces.To do that, once again, you need to combine LEN with SUBSTITUTE.
The SUBSTITUTE writing to count characters without spaces is more direct compared to the previous two writings. Here is how it is written with LEN generally.
=LEN(SUBSTITUTE(text,” “,””))
In the writing above, SUBSTITUTE will replace all spaces in your text with empty data (represented by two quotes signs without any space (“”)). This causes the text from which you count the number of characters is removed from all its spaces. Because of this, LEN will count the number of characters in your text without any spaces in it.
The example of this writing application in excel is as follows.
The screenshot demonstrates what has been explained about the writing before. The result is the characters count without spaces in your data as you need it to be.
Exercise
After you have learned completely how to use LEN, you can practice your understanding by doing this exercise!Download the exercise file from the following link and answer the questions. Download the answer key file if you have done the exercise and sure about your answers!
Link to the exercise file:
Download here
Questions
- How many characters are there in column B data?
- How many characters are there in column C data?
- How many characters are there in column D data?
Link to the answer key file:
Download here
Additional Note
- The formatting result of a number won’t be counted by LEN (the number of characters in a currency format, $3.00, of a 3 will still be counted as 3 (the same as the number of characters before the formatting), not 5)
- If used to count the characters in a date, LEN will count it from the excel number that represents the date (the number of characters in a “12/11/2020” date is 5 because the excel number that represents it is 44147)
Related tutorials you want to learn too:
