If you want to extract a substring from a Microsoft Excel spreadsheet, there are a few steps you can take. First, open the spreadsheet and select the cells that you want to extract the substring from. Then, use the following steps to extract the substring:
- Choose one of the Tools on the Ribbon and click on it. This will open a dialog box where you can choose how to extract the substring.
- If you want to extract all of the cells in a row or column, select all of them and then click on Extract All. This will extract all of the cells in that row or column into one big block.
- If you only want to extract a specific cell, select it and then click on Extract Selected Cells. This will start extracting only that cell and won’t affect any other cells in that sheet.
If you want to extract a substring from the left, right, or middle of your text, you can use Microsoft Excel’s LEFT, RIGHT, MID, LEN, and FIND functions to do that. We’ll show you how.
Which Method to Use for Substring Extraction?
What method to use to extract a substring depends on where your substring is located.
To extract a string from the left of your specified character, use the first method below. To extract everything that’s to the right of your specified character, use the second method below. To extract a string from the middle of your text, use the third method below.
RELATED: 12 Basic Excel Functions Everybody Should Know
Get the String To the Left of Your Text
If you’d like to get all the text that’s to the left of the specified character in your cell, use Excel’s LEFT and FIND functions to do that.
First, open your spreadsheet and click the cell in which you want to see the result.
In your selected cell, type the following function. In this function, replace B2 with the cell where your full text is and @ with the search character. The function will retrieve the entire string to the left of this character.
Then press Enter.
Your selected cell will display the result of the function, which is the full text before your specified character in your cell.
You’re all set.
Extract the String to the Right of Your Text
To get all the text that’s to the right of the specified character in your cell, use Excel’s RIGHT , LEN , and FIND functions.
Start by launching your spreadsheet and clicking the cell in which you want to see the result.
In the selected cell, enter the following function. In this function, replace B2 with the cell where your full text is and @ with the search character. Excel will extract the entire string to the right of this character.
Then press Enter.
You’ll see the result of the function in your chosen cell.
You’re done.
RELATED: 13 Essential Excel Functions for Data Entry
Obtain a String From the Middle of Your Text
If you’d like to extract a string containing a specific number of characters located at a certain position in your cell, use Excel’s MID function.
In your spreadsheet, select the cell where you want to display the resulting string.
In the selected cell, enter the following function. In this function, replace B2 with the cell where you have the full text, 1 with the position of the character where you want to start the string selection, and 3 with the number of characters you want to extract.
Then press Enter.
Excel will extract the specified number of characters from the given position in your cell.
And that’s all there is to it.
Like this, you can also count the number of cells with text in your Excel spreadsheets. Check out our guide to learn how.
RELATED: How to Count Cells With Text in Microsoft Excel
