Sometimes while working with large Excel documents you may need to extract a certain text from a specific cell. It is one of the common tasks for people working with text data. Luckily, Excel offers its users easier ways of extracting texts. You can do so using text formulas or other in-built Excel features.
Let's have a look at some of the ways you can extract text from a cell in Excel in our guide below.
Using Excel Text functions to extract text from a cell
RIGHT function
With this Excel formula, one can extract the specified number of characters (substring) from the right of a text string in a spreadsheet.
You will use the formula =RIGHT(text, [num_chars]) where the text stands for the address of the cell that contains the source string, and num_chars stands for the number of characters you want to extract.
LEFT function
In Excel, we use the LEFT function to extract text from the left of a text string. The formula will be;
=LEFT (text, [num_chars])
FIND function
The FIND function is most useful when you do not know the number of characters.
MID function
When you are looking at how to extract a text that is in the middle of a string, we use the MID function. The function enables you to extract your text from a specified starting point in a given text string. Here is the formula to apply;
=MID (text, start_num, num_chars) here, start_num stands for the starting point of where you want to start extracting your text from the left.
LEN function
The LEN function is used when you want to return the number of characters in a text string.
Steps to follow when using the RIGHT, LEFT & MID functions
1. In an Excel Office, open the worksheet that contains the text you want to extract.
2.
In your data list, please select a blank cell that you will want to place your extracted result.
3. Use the formula for either the MID, RIGHT, or LEFT functions depending on which text you want to be extracted.
4. Type the formula and enter the arguments for the function in your formula bar. For example, you can use the formula =LEFT (B3, 3) to show that you want to extract text from cell B3. 3 is the number of characters you want to extract in the cell.
5. Press ENTER to get your results.
Using the FIND and REPLACE Excel feature to extract a text from a cell
When using this tactic, note that the original data is usually altered. Therefore, it is good to have a backup of your original spreadsheet data in case you make any mistakes.to use this feature;
1. Copy and paste the original data that contains the text you want to extract.
2. Select the data.
3. Go to your Home tab in the main menu.
4. Click on Editing > Find & Select to display a dialog box. You can also use the keyboard shortcut Ctrl + H.
5. In the dialog box that appears. Type in the required information on what you want to find. You can leave the Replace with section blank.
6. Click on Replace All. Doing so will extract and remove all the text you wanted within the text string.