There are many ways to manipulate text in Excel, including the LEN, LEFT, RIGHT, and MID functions (among others). These functions all help us manipulate strings of characters in different ways to get the length or to extract different sections of 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 returns the length, in characters, of the string of text. LEN takes a cell reference or a string of text enclosed in quotes as its argument.
The function above returns 9 because the string of text comprises 9 characters. LEN counts spaces and punctuation but does not count the quotes that enclose the text argument.
If cell A1 contains the text "Other text!!" then =LEN(A1) would return 12.
The LEFT function in Excel finds the leftmost characters of a string of text to a certain length. It takes two arguments. The first is a string of text, either as a cell reference (such as B1) or as an actual string enclosed in quotes (such as "hello"). The second argument is the number of characters you want to return, including letters, spaces, and punctuation.
The function above returns "Exc" because it is the three leftmost characters of the first argument. =LEFT("Excel Exercises", 14) returns "Excel Exercise" because this is the string of the 14 leftmost characters of the first argument.
The RIGHT function in Excel is similar to LEFT, except it finds the rightmost characters of a string of text to a certain length. It also takes two arguments. The first is a string of text, either as a cell reference (like B1) or as an actual string enclosed in quotes (like "hello"). The second argument is the number of characters you want to return, including letters, spaces, and punctuation.
The function above returns "ses" because it is the three rightmost characters of the first argument. =RIGHT("Excel Exercises", 9) returns "Exercises" because this is the string of the 9 rightmost characters of the first argument.
The MID function in Excel returns text of a certain length from the inside of a string (neither the leftmost nor the rightmost). The MID function takes three arguments:
The function above returns "cel Exe" because it starts on the third character of the string and extracts 7 characters total (including the space).
Suppose you had the following message in cell A1:
You only want to extract the piece that says "GOOD", so you can write the following MID function:
This returns "GOOD" because we start at character number 5 (the "G") and extract a string 4 characters in length.
The LEFT function in Excel takes a certain number of the first, or leftmost characters from a string of text. LEFT works with a text argument, or with a cell reference. The LEFT function below takes a text argument. It takes the four leftmost characters from the string to return "Step".
=LEFT("Step Brothers", 4)LEFT also works with a cell reference argument. Say we have cells which contain an employee's 4 digit ID, first name, and age all in the same cell. We can use LEFT to extract the ID from each cell.
| A | |
|---|---|
| 1 | 1234 Bob 32 |
| 2 | 5678 Jennifer 45 |
=LEFT(A1, 4)=LEFT(A2, 4)The formulas above would return "1234" and "5678", respectively.
The RIGHT function in Excel takes a certain number of the last, or rightmost characters from a string of text. RIGHT also works with a text argument or with a cell reference. The RIGHT function below takes a text argument. It takes the eight rightmost characters from the string to return "Brothers".
=RIGHT("Step Brothers", 8)RIGHT also works with a cell reference argument. Say we have cells which contain a city and state in the same cell. We can use RIGHT to extract the state from each cell.
| A | |
|---|---|
| 1 | Minneapolis, MN |
| 2 | Chicago, IL |
| 3 | Boise, ID |
=RIGHT(A1, 2)=RIGHT(A2, 2)=RIGHT(A3, 2)The formulas above would return "MN", "IL", and "ID" respectively.
The example above runs into a problem if the strings are of variable length. For a more complex example, suppose you have a string that is an employee's ID number, first name, and age:
| 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: