How to Count Characters Using the LEN Formula in Excel

By Indeed Editorial Team

Updated November 15, 2022

Published May 2, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Excel is a tool that businesses within all industries benefit from worldwide. It can help professionals perform various processes, including budgeting, stock analysis, or organizing client sales lists. Understanding how Excel's functions work, such as the LEN formula, can help you manage data in a spreadsheet effectively. In this article, we explain the LEN formula in Excel, provide you with an example to understand it, and list some tips when using the LEN formula.

Related: Advanced Excel Skills: Definitions and Examples

What is the LEN formula in Excel?

The LEN formula in Excel is a calculation that can count all the characters in a string, which is a block of text which can include letters, numbers, spaces, and special characters. This formula's name, LEN, is short for length, and its output provides the total length of the string as a number. The formula requires one argument, the text you wish to count the length. You can provide the text argument for the function directly, as a reference to a cell containing a string, or a string from another formula. Here's the LEN formula:

=LEN(text)

Below are the elements of the LEN formula in more detail:

  • LEN: This is the formula Excel uses to calculate the length of a string.

  • Text: This is the string, which is returning the length of the data.

Related: 5 Basic Excel Skills and How to Include Them in Your Resume

LEN formula example

If you have a spreadsheet with various names, you can use the LEN formula to calculate the number of characters in the string. If cell A1 has the string Kim Bach, you can use the LEN syntax =LEN(A1). This formula has a return output of eight because of the seven letters in the name and the extra space between the first and last name. If cell A2 has the string Sal Jones-Gale, you can use the LEN formula as =LEN(A2). This has an output of 14 because of the 12 letters, the dash, and the space in the name.

Related: How to Learn Data Entry and Available Career Options

Combining LEN with other string formulas

To extract elements from a longer string, you can combine LEN with other string formulas such as TRIM, LEFT, MID, RIGHT, and SEARCH. It's essential to know the output each formula returns to determine the right formulas to combine with the LEN formula. The following explains how you can use these string formulas with the LEN formula:

Using TRIM to remove leading and trailing spaces

You can use the trim formula to remove any spaces within a text string except for the spaces between the words. The result displays the remaining characters. Placing the TRIM formula as an argument in the LEN formula instead of text can tell Excel to calculate the number of characters in the string after trimming the spaces before and after the string. Here's how the LEN formula looks with the TRIM formula as an argument:

=LEN(TRIM(text))

Here's an example using this combination:

=LEN(TRIM(“ Happy Birthday! “)

The output of this formula is 15 because there are 15 characters in the string, including the exclamation mark, without the spaces before and after it. Without the TRIM formula as an argument, there are 17 characters.

Removing all spaces

You might also use the LEN formula to count all the characters in a string, but not the spaces. To do this, you can use the LEN and SUBSTITUTE formula combination. Here's how the LEN formula looks with the SUBSTITUTE formula as an argument:

=LEN(SUBSTITUTE(text, old_text, new_text, [instance_num])

Below are the elements of this formula in more detail:

  • Text: This is the string that the LEN formula is counting its characters.

  • Old_text: This is the text that the SUBSTITUTE formula is replacing.

  • New_text: This is the replacement text.

  • Instance_num: This specifies which occurrence of the old text the SUBSTITUTE formula is replacing. This argument is optional.

For example, if you're using the same string,“ Happy Birthday! “, and you're attempting to count the characters with none of the spaces in the string, you can use the formula =LEN(SUBSTITUTE(“ Happy Birthday! “, “ “, “”)). The formula's output is going to show 14 since that's how many characters there are in the string with no spaces. It's essential to remember that there's a space between the quotation marks for the old_text argument. No characters or spaces are between the quotation marks for the new_text argument.

Counting strings of variable length

You can combine LEN with various formulas to count the characters in a specific portion of a string in Excel. This can be useful when the text string follows a specific format but can be of variable length. It's essential to remember the type of data each formula returns and how you can incorporate the data into a useful formula. The following are some LEN formula combinations you can use to count strings of variable lengths:

Using LEN with SEARCH

You may have information in different cells that can follow a specific pattern, such as a list of product codes. Each product code may have different numbers while following the pattern. For example, the codes can be AG-28-252, BG-786-694, or EC-68-94. You can see there are two or three numbers to the right and left of each dash. The number of characters may be important when performing inventory management throughout the year. If you're looking to identify which product code has three digits in the last group, use this formula to find the number of characters after the second dash:

=LEN(text) - SEARCH(find_text, within_text, [start_num])

Below are the elements of this formula in more detail:

  • Find_text: This is the character or substring you're locating.

  • Within_text: This is the string or cell reference in which you're looking for characters.

  • Start_num: This is the place number of the character to start your search. This argument is optional, and if you leave this empty, the formula starts looking from the string's first character.

For example, you can place the product code AG-28-252 in cell A2 on the spreadsheet, BG-786-694 in cell A3, and EC-68-94 in cell A4. Using this information, you know that the first dash is always the third character, so you're looking for the fourth and subsequent characters. You can use the formula =LEN(A2) - SEARCH(“-“, A2, 4). This formula can determine the length of the string and subtract the result of the SEARCH formula. This has an output of three. You can then use the same formula for the remaining cells.

Related: 10 Careers in Logistics (With Salaries and Primary Duties)

Using the LEN and MID formula

Using the same product numbers, you can use the consistency of the first three characters in the string to count the number of characters in the middle. The MID formula requires a starting point. In this case, you can use the fourth character. The formula also requires the number of characters in the middle string. In this example, you can find out what the middle value is. After the first dash, you can treat everything as the middle string by specifying a large number.

The syntax for MID, for this example, is =MID(A2,4,100). You can place this output in B2 to use in the rest of the formula. Here's the formula you can use to count the numbers of characters in the middle of a string:

=LEN(LEFT(B2,SEARCH(“-“,B2)-1))

Below are the elements of this formula in more detail:

  • SEARCH(“-“, B2) - 1: This returns the position number after searching B2 for a dash and subtracts 1. The value 28-252 has a dash in the third character, and after subtracting 1, you have a value of 2.

  • LEFT(B2, SEARCH(“-“, B2) - 1): This uses the result of the SEARCH formula as the second argument for the LEFT formula. The LEFT formula reads the string in B2 and returns the 2 left characters, 28.

  • LEN(LEFT(B2, SEARCH(“-“, B2) - 1)): This counts the characters in the formula, 28. The output of this formula is two.

LEN formula tips

The following are some tips that you can remember when working with the LEN formula in Excel:

  • You can use the formula on numbers and dates.

  • The formula output returns a number.

  • The formula doesn't include formatting length. For example, when calculating the length of 100 when formatting the string as $100.00, the output is still three.

  • The LEN formula returns an output of zero if the cell is empty.

  • The formula can only apply to one cell at a time.

  • LEN can accept a reference to a cell, for example, A2, or the text directly as an argument.

  • Double quotes are a requirement when adding text directly to the argument.

  • Double quotes aren't a requirement when adding numbers directly to the argument.

  • When working with decimals, excel can only store 15 numbers in a cell. As a result, the maximum output the LEN formula can return is 16, which is the 15 numbers plus the decimal.

Please note that none of the companies, institutions, or organizations mentioned in this article are affiliated with Indeed.

Explore more articles