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 left-most 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 left-most characters of the first argument. =LEFT("Excel Exercises", 14) returns "Excel Exercise" because this is the string of the 14 left-most characters of the first argument.
The RIGHT function in Excel is similar to LEFT, except it finds the right-most 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 right-most characters of the first argument. =RIGHT("Excel Exercises", 9) returns "Exercises" because this is the string of the 9 right-most characters of the first argument.
The MID function in Excel returns text of a certain length from the inside of a string (neither the left-most nor the right-most). 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 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:
|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:
As with all shortcuts and formulas in Excel, the best way to improve is to try as many hands-on practice exercises as possible. The more repetitions you get, the more quickly and naturally you'll be able to use them in your everyday work.