How to extract part of text string from cell in Excel?

This tutorial demonstrates methods to extract substrings from the left, middle, or right of a cell and explains how to extract text before or after a specific character, as shown in the screenshots below.

Extract substring from left, mid or right Method A: formula   Method B: handy tool Extract substring after or before a specified character Method A: formula   Method B: handy tool Extract substring between two characters Method A: formula   Method B: a handy addin tool Extract E-mail address from a string Extract numeric or alphabetical characters from string Download sample file Other Operations (Articles) Related To Spaces Extract nth character from string  Extract time from datetime So on..

Method A: Extract substring from left, middle or right by using formula

In Excel, there are some formulas can help you to quickly extract part of text.

Extract first n characters

Supposing you want to extract first 3 characters from a given list data, please select a blank cell that you want to place the extracted result, then use this formula

B3 is the cell you extract characters from, 3 is the number of characters you want to extract.

Press Enter key to get the extracted result. Then drag fill handle over the cells to apply this formula.

Extract last n characters

For example, extract last 6 characters from a list of string, please select a blank cell that you want to place the extracted result and use this formula:

B9 is the cell you extract characters from, 6 is the number of characters you want to extract.

Press Enter key to get the extracted result. Then drag fill handle over the cells to apply this formula.

Extract n characters from the middle

If you want to extract 3 characters begin from the 4th character of a string, you can use below formula:

B15 is the cell you extract characters from, 4 represent extract characters from 4th character (count from left), 3 is the number of characters you want to extract.

Press Enter key to get the extracted result. Then drag fill handle over the cells to apply this formula.

Method B: Extract substring from left, mid or right by Kutools for Excel

If you are not familiar with formulas, you can try Kutools for Excel’s Extract Text feature which is easy to handle this job.

1. Select the cells that you want to extract substrings from, click Kutools > Text > Extract Text.

2. In the popping Extract Text dialog, under the Extract by location tab, the first three options support you to extract substring from left, mid or right.

The first N character: extract substring from left. For instance, extract first 2 characters, check this option and type 2 into textbox.

The last N character: extract substring from right of the string. For instance, extract last 2 characters, check this option and type 2 into textbox.

Start to end characters: extract specific number of characters from middle for string. For instance, extract from 4th character to 9th character, check this option and type 4 and 9 into textboxes separately.

Insert as a formula: check this checkbox, the result is a formula which can be changed as the original string change, otherwise, the result is fixed.

3. After specifying the location as you need, click Ok, a dialog pops out to select a cell to place the extracted substring. Click OK.

Now the substring has been extracted.

If you want to extract a substring after or before a specified character, you can apply one of the methods below to complete the task.

Method A: Extract substring after or before a defined character by using formula

Supposing you want to extract characters after the character “-” from a list of strings, please use this formula:

B3 is the cell you want to extract characters from, - is the character you want to extract string after.

Press Enter key to get the extracted result. Then drag fill handle over the cells to apply this formula.

If you want to extract substring before a defined character, you can use the formula like this:

Example result is shown as below:

Method B: Extract substring after or before a defined character by Kutools for Excel

To directly extract a substring after or before a specified character, you can use the Extract Text utility of Kutools for Excel, which can help you to extract all characters after or before a character, also can extract specific length of characters before or after a character.

1. Select the cells that you want to extract characters, click Kutools > Text > Extract Text.

2. In the popping Extract Text dialog, under Extract by location tab, go to the before the text and after the text options to specify the setting as you need..

Before the text: extract substrings before the entered character(s). For instance, type – into the textbox, all characters before – will be extracted.

After the text: extract substrings after the entered character(s). For instance, type – into the textbox, all characters after – will be extracted.

Insert as a formula: check this checkbox, the result is a formula which can be changed as the original string change, otherwise, the result is fixed.

3. Click Ok, a dialog pops out to select a cell to place the extracted substring. Click OK.

Now the string before or after the certain character(s) has been extracted.

Maybe in some cases, you need to extract substring between two characters, you can choose one of below methods to handle the job.

Method A: Extract by formula

To extract characters between brackets () from a given list, you can use below formula:

In the formula, B3 is the cell that you want to extract string from, ( and ) are the two characters you want to extract string between.

Press Enter key to get the extracted result. Then drag fill handle over the cells to apply this formula.

Method B: Extract by Kutools for Excel

In Kutools for Excel’s hundreds of features, there is a feature – Extract strings between specified text can quickly extract substrings between two characters.

1. Select a cell that used to place the extracted substring, click Kutools > Formula Helper > Text > Extract strings between specified text.

2. In the Formulas Helper dialog, go to the Arguments input section, then select or directly type the cell reference and the two characters you want to extract between.

By default, when you select the cell reference used for extracting the substring, it will be absolute, preventing the use of the auto-fill handle. Please change it to relative.

3. Click Ok, now the first result has been gotten, then drag auto fill handle over cells that need this formula.

If you want to extract e-mail address from a given string or a range of cells, you can use the Extract Email Address function to handle this job at once time instead of finding them one by one.

1. Select the cells that will be extracted email address, then click Kutools > Text > Extract Email Address.

2. Then a dialog pops out for you selecting a cell to output the address emails.

3. Click OK, the email addresses in each cell have been extracted.

If your data includes a mix of numeric, alphabetical, and special characters, and you only want to extract the numbers or alphabetical values, try Kutools for Excel's Remove Characters utility.

1. Before using the Remove Characters utility, make a copy of the data as shown in the screenshot below.

2. Then select this copy of data, click Kutools > Text > Remove Characters.

3. In the Remove Characters dialog, check Non-numeric option, click Ok.

Now, only the numeric characters remain.

To extract alphabetical values only, check Non-alpha option in the Remove Characters dialog.

Click to download this sample file