LEN Function In excel and how to use it?

The Len Function Does What?

The Len function helps you find the number of characters in a excel cell. Just type = LEN ( select any cell with characters), it will show you the number of characters in it.

The Len Function is categorized under the Excel String/Text function. The function returns the length of the specified string or in other words lenght of the text. It can be used as a worksheet function and a VBA function in Excel. As a WS function, the LEN function can be entered as a part of a formula in a cell of a worksheet. As a VBA function, the user can use this function in a macro mode that is entered through the Microsoft Visual Basic Editor.

More about Len Function 

  1. Using Len Function In Excel
  2. Important Note While using the Len Function
  3. Example
  4. How to Exclude Spaces while using the function
  5. Real Application of Len function
                      1.  

Using Len Function In Excel

  • Syntax: Len Formula

          LEN (text)

  • Parameters:
  1. Text – The string to return the length for
  • Returns:

          The LEN function returns a numeric value

  • Applies to:

         Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003,               Excel XP, Excel 2000

Important Note While using the Len Function

Len function will count the number  blank spaces as characters too. For example in a cell. As you can see the first case, the function has also calculated the two spaces in between.

Hence be care full while making assumptions on the character length.

Example

Let’s see how we can test a few conditions. Suppose we are given the following values: 

We will get the results below:

In the examples above, 

  1. Spaces are counted as characters. Hence, the function returned 13 for the first argument.
  2. The length of the empty string is 0.

How to Exclude Spaces while using the function

This can be a little tricky to understand at first but get the logic here:

  • Lets say we have spaces between the characters like our previous example.
  • Now the spaces in excel can be denoted as (“ ”), meaning blank space
  • And the notation without space would be (“”)
  • So we can substitute, the “ “ with “”, using the substitute function

Here is how it will look: Now the Len function will give you the same answer as if there was no space.

A similar relative method can be used to substitute punctuation, in the example below we have substituted the “-“ with “”.

Which means, we want excel to ignore the “–“ in the counting

Real Application of Len function

Len function although very sparsely used in daily life, can be used to build some logic.

For example: Lets design a cell in an excel tempalte, which checks, if the characters of an ID card is entered correct or not.

  • Lets say the ID card should have 8 characters only
  • If the characters are less than 8 or more than 8 are entered, then it throws an error

  • So you can see we used a logical operator and the logical test uses len function  counts the number of characters
  • Which we are testing it to be equal to 8
  • If it returns true , then no error is shown or its “”- blank

  • Else it throws our error message

This was just an illustration of a simple use case of Len function, based on the industry you are working. There can be many such interesting applications of it. You can check out such applications here :

 

Default image
Sarthak Bhalerao

Leave a Reply