There are three Excel substring functions that are very powerful, yet easy to use. These functions are used to extract a substring of text from a cell on your spreadsheet. Which one you use depends on how you want to extract the text.
The three text substring functions are LEFT, RIGHT and MID. Let’s look at them one at a time.
Excel Substring: LEFT Function
The LEFT function extracts a specified number of characters from a string of text, starting with the leftmost character. For example, assume we have the following text in cell A1 in our spreadsheet:
The quick brown fox jumps over the lazy dog
If we want to extract only the phrase, “The quick brown fox,” we would use the following LEFT function:
This function tells Excel to look at the text value in cell A1 and then take the leftmost 19 characters.
Excel Substring: RIGHT function
Now, suppose we want to do the same thing, but starting at the right side of the text. This is where the RIGHT function comes in handy.
If we want to extract only the phrase, “the lazy dog”, we would use the following RIGHT function:
This function tells Excel to look at the text value in cell A1 and then take the rightmost 12 characters.
Check out our Excel Lesson Videos to learn how to use more text functions in Excel.
Excel Substring: MID function
You can probably guess what the MID function does. It extracts a substring of text from the middle of a cell’s text value.
However, the MID function has a slightly different set of parameters. Instead of just specifying the original text value and number of characters to extract, we now also need to specify the starting position in the original text.
If we want to extract only the phrase, “brown fox jumps”, we would use the following MID function:
This function tells Excel to look at the text value in cell A1, count from the left until you get to the 11th character and then take the next 15 characters (including the 11th character).
With the LEFT, RIGHT and MID functions at your disposal, you have a powerful set of tools to extract a substring of text in Excel.