Excel LEN Function – How To Use

The LEN Function, also called the "length" function in Excel, determines and returns the total length of the given string. To calculate the length, the LEN function counts the total number of characters in the input string, including alphabets, numbers, special characters, non-printable characters, and all spaces from a cell in Excel.

Syntax

The syntax of the LEN function is as follows:

The LEN function only accepts one mandatory argument.

Arguments:

'text' – This is an input argument that can be passed directly in double quotes. The text argument can be both in text or numeric format. The text argument can also be a cell reference containing the input string or a formula that returns a string whose length needs to be calculated.

Important Characteristics of the LEN function

Some of the noteworthy characteristics of the LEN function are given below.

• If the cell contains an error, the LEN function returns the same error.
• The LEN function ignores the number formatting of the data.
• The length of an empty string is 0.

Examples of LEN Function

As the LEN function calculates the length of the input string, let's try using the most basic functionality of the LEN function using examples.

Example 1 – Simple Use of LEN Function

We have a list of movie names in column B, and we will simply try to use the LEN function to calculate the length of the input string.

The formula used will be as follows.

As observed while calculating the length of the movie names, the LEN function includes alphabets, numbers, spaces, special characters, and non-printable characters.

In the first example, the LEN function simply calculates the number of characters, including the spaces. The second example contains extra spaces which are counted in the LEN function as seen in column C.

The example in cell B6 contains an emoticon which, as seen, is also counted by the LEN function. The word "Interstellar" is 12 characters long. Combining one space and a special character makes the total length of the string which is 14. The last example is where the string is 4 letters long, but the LEN function returns 5 as the input string contains a non-printable character.

Now that we know what the LEN function includes while calculating the length let's understand if it behaves similarly with currency, date, and time.

Example 2 – LEN Function with Number Format Data

Excel offers in-built functionality for using number formatting such as date, time, currency, and more. The number formats in Excel only change the data's appearance without altering the value. Therefore, when the LEN function is applied to formatted data, the function returns the length of the actual value instead of the formatted value.

Let's check it with an example.

We have different Number Formats in this example, such as date, time, currency, and normal number rounded off to 5 places in column C. The formula used is as follows:

All the return values of the LEN function is are in column D. As observed, the first example in cell C3 is the date in the format mm/dd/yyyy. As per the format, the total length of the data is 10, but the LEN functions return 5. Excel stores date and time as sequential serial numbers. So, as per Excel, the date in cell C3 is actually 44844, hence the return value of the LEN function.

The same is the case with the second example in cell C4, where the time is shown in the format hh:mm, but actually, Excel reads it as 0.729166666666667 (time is stored as a decimal in Excel); therefore, the return value of the LEN function is 17.

In the third example in cell C5, the currency formatting is ignored by the LEN function, and it returns the length of the actual value, which is 4 i.e. just the number of characters of the number 1000.

In the last example, the value of cell C6 is =5/7

Using the Increase Decimal button in the Number group, we have expanded the number in C6 to its complete decimal value. Including the 0 and the decimal point, you'll arrive at the total count of characters as 17. The LEN function returns the length of the complete result instead of the rounded-off value, which can be seen in cell C6.

Therefore, it is important to remember that the LEN function always returns the length of the actual value instead of the formatted value that is displayed.

Example 3 – Calculating Length without Extra Spaces

We often download data from the Internet for analysis or reference. Upon downloading, the data usually contains unnecessary spaces and non-printable characters. In that case, the LEN function will include the extra spaces and characters which might skew our data.

To resolve it, we can combine the LEN function with other functions like the TRIM function and the CLEAN function.

In this example, we downloaded a few famous quotes from the internet to further post on social media. Assuming that the social media post has a character limit, we must check the length of the quotes before posting them.

As we can see, there are additional spaces in the downloaded data, and the last one even contains a line break. The length of the string, if taken from the original format, will be incorrect as it will include all the additional spaces and non-printable characters.

To get rid of all the additional spaces, we can use the TRIM function, as it removes all unnecessary spaces leaving behind one space between words. To remove the line break, we can use the CLEAN function. The formula used will be as follows.

Now, we can assess if the length of the strings fit our character limit and then publish them on social media after the clean-up. To get the final quotes after cleaning, simply use the CLEAN and TRIM functions. The formula used will be as follows.

For reference, let's compare the input string length before cleaning up additional spaces and characters.

As we can see, column D contains the return value of the basic LEN function, which is significantly greater. After cleaning up the input data, the LEN function could return a more accurate value of the length of the input string.

Now that we have understood how to deduce the length of the string without any additional spaces or characters, what if we wish to get the length of the string without any spaces?

Example 3.1- Calculating Length without any Spaces

In this example, we have a list of email addresses for our mailing list. As per the standard, the maximum character limit of the email IDs must be 320 characters, and no space is accepted in a valid email address.

As the list has been downloaded, it is important to ensure that there are no spaces, and after spaces, the total length of the email IDs is at most 320. The formula used to calculate the length of the string without any spaces is as follows.

The SUBSTITUTE function replaces all the spaces (denoted in the formula by " " in the second argument of the SUBSTITUTE function) with an empty string ("" in the third argument of the SUBSTITUTE function), and then the LEN function calculates the length of the string.

Further, we can check if the return value of the LEN function is less than 320, then we can remove all spaces using the SUBSTITUTE function for final email IDs. The formula used will be as follows.

Example 4 – Removing Characters Using the LEN Function

The LEN function is often used with other Excel functions to manipulate text strings. One of the most common applications of the LEN function is with LEFT and RIGHT functions. Suppose the dataset contains names of female athletes along with their weight. Now, we wish to calculate the average weight of female athletes.

As the weight is mentioned along with the unit pounds, we will not be able to calculate the sum or average. Let's learn how to remove the last few characters from the data using the LEN function.

The formula used will be as follows.

The LEFT function extracts the given characters from the left of the string. To calculate the number of characters to extract we simply subtract the length of the word "pounds" along with an extra space from the complete length of the input string. Hence, the formula will be as follows.

We finally removed the additional unit characters from the data. Now to convert the final value in column D from the text format to numeric format, we can wrap it in the VALUE function.

We can finally calculate the average weight by using the direct formula as all the data is clean and in numeric format.

Example 5 – Counting Words in a String

We have a dataset of a few inspirational quotes, and we wish to count the number of words in each. To determine the number of words in each quote, we will use a simple observation that the number of words in a sentence is usually the number of spaces plus 1.  The LEN function plays a significant role in putting this logic in Excel format.

To calculate the number of spaces in each quote, we will find the length of the sentence with the usual one space in between words and then calculate the length without any spaces. We will then subtract the return value of both LEN functions. Finally, we will simply add 1 to get our final count of words.

Let's create the Excel formulas for every step. Calculating the length of the string with usual spaces, we will combine the LEN function with the TRIM function. The formula used will be as follows.

The TRIM function removes all unnecessary spaces leaving just 1 space between each word. Next, calculating the length of the string without any spaces, the formula used will be as follows.

Then, subtracting both and adding 1, the final formula used will be as follows.

We finally have the number of words in each string.

We hope you understood all the aspects of the LEN function. Practice and find more ways to use the LEN function while we prepare another intriguing Excel tutorial for you.