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.







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.

How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps - Screenshot of the Example of LEN's Usage and Result

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.



  1. Type an equal sign ( = ) in the cell where you want to put the number of characters in your data

    How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps - Screenshot of Step 1

  2. Type LEN (can be with large and small letters) and an open bracket sign after =

    How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps - Screenshot of Step 2

  3. Input the data you want to count the number of characters of or the cell coordinate where the data is

    How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps - Screenshot of Step 3

  4. Type a close bracket sign

    How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps - Screenshot of Step 4

  5. Press Enter
  6. Done!

    How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps - Screenshot of Step 6




“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:

How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps - Screenshot of Before Using TRIM on LEN

After:

How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps - Screenshot of After Using TRIM on LEN

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.

How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps - Screenshot of the Example of SUMPRODUCT LEN

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.

How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps - Screenshot of the Example of LEN SUBSTITUTE to Count a Specific Caharacter

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.

How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps - Screenshot of the Example of SUMPRODUCT LEN SUBSTITUTE

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.

How to Use Excel LEN Function/Formula: Usability, Example, and Writing Steps - Screenshot of the Example of LEN SUBSTITUTE to Count Characters Without Spaces

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

  1. How many characters are there in column B data?
  2. How many characters are there in column C data?
  3. 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:



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on




Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert