LEN Function in Excel – Find Number of Characters

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

=LEN(text)

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.

List of Words in Excel To Calculate Length

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.

=LEN(A2)

As a result, excel would return the letter count.

Using LEN Function in Excel - Basic Example

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.

List of Numbers in Excel Cell

To calculate the number of digits count, simply use the following formula in cell B2, and drag it to other cells:

Infographic - LEN Formula Function in Excel
=LEN(A2)
Counting Number of Digits in Excel Cell

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:

Count Number of Words in Excel - Sample Example
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

As a result, excel would return 6 as the output.

Finding Number of Words in Sentence in Excel

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 🙂

Leave a Comment