Count the Number of Characters in a Cell – Excel LEN Function

In this tutorial, we will learn about Excel LEN Function, basically, we use this function to count the length of the cell string or value. This function counts every single character including a single space too.

Why we use this?

 

Excel LEN function is very useful to count or find the length of the cell value. This function counts the exact length of DATE and numeric value in a cell with different formats.

 

What are the syntax for LEN?

=LEN (text)

 

Arguments or Parameters

Text – this is the text to find the length

What it returns as result?

Excel LEN function will return its result in a numeric form.

 

Important note:

 

Excel LEN function is helpful when we need to count the number of character in a cell value.

It will give the result as “0” if the cell value is blank.

This function will return a result as an error if a cell has an error value.

We can insert text string directly into the function

This function return result as #VALUE! Error when we add more than one cell value in a range.

Number and Date formatting will be ignored in this function and will return a length.

 

How Excel LEN Function find a length

 

Here, LEN function is counting each of the characters in every cell including space. We can write the string directly within double quote instead of using a cell reference.

 

Excel LEN Function

 

 

In the above example, we have used both ways to find the length of the string by using a cell reference (B2) and write the text string directly with a double quote.

Note: A text value always will be written within a double quote sign, so Excel LEN function is ignoring these signs.

 

Excel LEN Function with Blank Cell

 

Here we will check the length of the blank cell in Excel.

Excel LEN Function

This function will return the result of a blank cell as a “0” character.

 

Length of a cell with error value

 

If a cell contains any of the error value, then the result as a length of that cell will be the same error. See image below:-

Excel LEN Function

 

Applying the LEN function within a range

 

Suppose that we want to count the length of a range, as appearing in the below image.

 

Excel LEN Function

 

This function return result as #VALUE! Error when we add more than one cell value in a range.

 

How to count the length of a range.

 

There are other ways to count the length of a range:-

 1. Using SUM with LEN as an array

 

First of all, we will apply the LEN function to count the length of each cell in a different column. And after that, we will add each of the LEN function to find the sum of all the cells within the range.  See example below:-

Excel LEN Function

 

This is one of the simplest method to count the length of the range of cells.

Or

We can use another methods, which is using SUM with LEN function with a cell reference. See below:-

 

Excel LEN Function

 

=SUM (LEN(B14),LEN(B15),LEN(B16))

But here problem is, if the list is huge then these methods are going to be lengthy. So better to use another one, which is here below.

With the help of array function, we can find the result in an easier way. See below:-

 

Excel LEN Function

 

In the above image, we use SUM and LEN functions as an array.

In the above example, write the formula =SUM(LEN(B14:B17)) and press “Cntr+Shift+Enter” otherwise it will not work and show the result as #VALUE! Error.

 

Note: – Before applying the array function, un-merged the cells first and then apply array function otherwise it will not work.

 

Excel LEN Function with Number and Date formatting

 

(a) Excel LEN Function with numeric values

 

In the below-given image, we have some numeric values with different formats.

Now we will apply the Excel LEN function to check the length of each numerical value.

Notice that this function doesn’t change the length of any number value whether it has a different format too.

Excel LEN Function

 

(b) Excel LEN Function with Date values

 

Here in the below image, we have some dates with different formats. Now we will check the length of each of the formats with LEN function.

 

In the above image, every format of date has different a number of characters but Excel LEN function find the length for all the date values which is 5.

To check the original format of these date value, we need to right click in the cell “B29” or date “13 March 2018” and choose general formatting option from the category.

In this image, we can see that the date format “13 March 2008” has 11 characters in itself but the actual format of this date is showing in the circle which is 5 characters.

 

Excel LEN Function

 

This is the original format of Excel date values. Excel LEN function will count every format of date values as 5 characters.

 

Related Post

Excel LEFT Function, Excel RIGHT Function, Excel MID Function, Excel FIND Function 

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.