There are many ways to manipulate text in Excel, including the LEN, LEFT, RIGHT, and MID functions (among others). The LEN function (which is short for "length") in particular helps us by returning the length of a string of text. A "string" of text in Excel is just any text, which can be in a cell or enclosed in quotes. Once we know the length of a string, we can combine LEN with other functions to perform the necessary changes or calculations we need based on the text. We often think only of numbers when working with Excel, but you may encounter lists of text values where you need to manipulate the list to strip out extraneous pieces of text and be left with the values that matter.
The LEN function takes one argument (value within the parenthesis) which is typically text, but could also be numerical or a date value. LEN then returns the length, in characters, of the argument. LEN includes characters such as spaces and punctuation in the character count. LEN can take a cell reference which contains text or a string of text enclosed in quotes as its argument.
=LEN("Some text")The function above returns 9 because the string of text comprises 9 characters. See below:
=LEN("Some text")------123456789While LEN counts spaces and punctuation, it does not count the quotes that enclose the text argument.
If cell A1 contains the text "Other text!!" then =LEN(A1) would return 12 because the LEN function counts the space and each exclamation point as an additional character.
The LEN function in Excel works with a text argument in quotes, or with a cell reference. Below are some examples of the LEN function with a cell reference argument.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | NAME | ID | AGE | SALES |
| 2 | Bob | 1234 | 32 | 5,000 |
| 3 | Jennifer | 5678 | 45 | 7,250 |
=LEN(A2)The LEN formula above would return 3, as shown below:
Bob123
=LEN(A3)This LEN formula would return 8. Why? The string "Jennifer" contains 8 characters.
Jennifer12345678
The LEN function can also be used on numbers in Excel.
=LEN(C2)This formula would return 2.
3212
In Excel, the LEN function can be used on numerical values to retrieve the number of digits in the number. When retrieving the number of digits of a number, LEN will NOT count formatting characters in the total. For example, =LEN(D2) from the Excel table above would return 4. The comma is considered a number formatting character, and the actual numerical value (5000) has 4 digits.
5,0001 234
Keep in mind that this is only the case for numerical values. In Excel, a cell may contain what appears to be a number, but is actually a text value. In this case, every character (including the comma) is counted.
=LEN("5,000")This formula would return 5, because the quotes tell us that the argument is a text value rather than a numerical value.
5,00012345
As with any Excel function which returns a numerical value, LEN can be used as an input to any function which needs a numerical argument.
Take the example below where we use the LEN function as inputs to the SUM function:
=SUM(LEN("I"), LEN("Love"), LEN("Excel"))
I
1
Love
1234
Excel
12345
The first LEN function returns 1, the second returns 4, and the third returns 5, so the entire SUM function would return 10.
The LEN function can be combined with other functions in Excel, such as the MID function, to help you separate unwanted characters from strings.
The table in the last example is nicely separated into separate columns for the employee's name, ID, age, and sales. Now suppose your boss sends you an Excel file with a single string for each employee, which contains their ID, name, and age, all in the same cell:
| A | |
|---|---|
| 1 | 1234 Bob 32 |
| 2 | 5678 Jennifer 45 |
You know that ID is always 4 characters and age is always 2 digits, but name can be any length. =MID(A1, 6, 3) will work to return "Bob", but the same function applied to A2 will only return "Jen" for Jennifer.
How do you write a reusable function to extract each employee's first name?
You know the length of the whole string in A1 is =LEN(A1). You also know that the name always begins at the 6th position of the string (because of the 4 digit ID + the space), and that there are 8 characters in each string that you DON'T want (ID, age, and two spaces).
Therefore, you can write the following function to return "Bob" when applied to cell A1, and return "Jennifer" when applied to cell A2:
=MID(A1, 6, (LEN(A1)-8))