Quickly extract certain text from cells in Excel

By default, Excel provides certain functions to extract text. For example, we can apply the LEFT or RIGHT function to extract text from the left or right of a string, or apply the MID function to extract text starting at the specified position, and so on. For many Excel users, remembering and applying formula is headache in daily work. Here highly recommended the Extract Text utility of Kutools for Excel, this powerful feature gathers a variety of ways to extract text from selected cells in bulk. Just simple settings can extract needed text from cells.

Click Kutools > Text > Extract Text. See Screenshots:

Extract first or last n characters from cells

This section is talking about extracting first or last n characters from cells. Please do as follows.

1. Click Kutools > Text > Extract Text to enable the feature.

2. In the Extract Text dialog box, configure as follows.

2.1) Make sure the Extract by location tab is enabled;

2.2) In the Range section, click the button to select the range of cells you will extract text from;

2.3) In the Options section:

(1) The first N character: To extract number of characters from the left of a string. Supposing you need to extract the first 2 characters from selected cells, please select this option, enter the number 2 into the textbox;

(2) The last N character: To extract number of characters from the right of a string. If you want to extract the last 3 characters from selected cells, please select this option, and then enter the number 3 into the textbox.

2.4) Click OK.

Note: Check the Insert as a formula box in the bottom left corner of the dialog will insert the result as formula into the result cells. When the value of the referenced cell changes, the result will update automatically.

3. In the opening Extract text dialog box, select a blank cell to output the result, and then click OK.

Now the first N characters or the last N characters of selected cells are extracted as the below screenshot shown.

Extract text by position from cells

Supposing you want to extract 4 characters starting at the 4th character in cells as the below screenshot shown, please do as follows to get it down.

1. Click Kutools > Text > Extract Text to enable the feature.

2. In the Extract Text dialog box, please do the below settings.

2.1) Click the Extract by location tab (This tab is enabled by default);

2.2) In the Range section, click the button to select the range of cells you will extract text from;

2.3) In the Options section: Select the Start to end characters option, specify the starting and the ending positions;

In this case, I want to extract 4 characters starting at the 4th character from selected cells, so I enter number 4 and 7 separately into the two textboxes.

2.4) Click OK.

Note: Check the Insert as a formula box in the bottom left corner of the dialog will insert the result as formula into the result cells. When the value of the referenced cell changes, the result will update automatically.

3. Then an Extract Text dialog box pops up, select a blank cell to output the result, and then click the OK button.

Then characters starting at the specified position and ending with the specified position will be extracted into the destination cells in bulk.

Extract text before or after specific string/character from cells

The Extract Text feature can also help to easily extract text before or after a specific string or character from cells, such as extract text before or after the hyphen character (-) as the below screenshot shown.

1. Click Kutools > Text > Extract Text to enable the feature.

2. In the Extract Text dialog box, please do the below settings.

2.1) Click the Extract by location tab (This tab is enabled by default);

2.2) In the Range section, click the button to select the range of cells you will extract text from;

2.3) In the Options section:

(1) Before the text: To extract text before a string or character. Select this option and then enter the string or character to extract all text before it (here I enter the hyphen character);

(2) After the text: To extract text after a string or character. Select this option and then enter the string or character to extract all text after it (here I enter the hyphen character).

2.4) Click OK.

Notes:

3. In the Extract Text dialog box, select a blank cell to output the extracted text, and then click OK.

Then the results are shown as the below screenshot shown.

Extract all numbers from text string in cells

There is no build-in feature in Excel to extract the numbers from a text string in Excel, the only way is to apply the combination of multiple functions to get it down. But with Kutools, extracting numbers from a text string can be easy to handle.

1. Click Kutools > Text > Extract Text to enable the feature.

2. In the Extract Text dialog box, please do the below settings.

2.1) Click the Extract by location tab (This tab is enabled by default);

2.2) In the Range section, click the button to select the range of cells you will extract text from;

2.3) In the Options section: Select the Extract the number option;

2.4) Click OK.

Note: Check the Insert as a formula box in the bottom left corner of the dialog will insert the result as formula into the result cells. When the value of the referenced cell changes, the result will update automatically.

3. In the Extract Text dialog box, select a blank cell to output the extracted text, and then click OK.

Then numbers from any position in a text string are extracted. See screenshot:

Extract specific text by rules

Apart from the above fixed options, The Extract Text feature allows you to create rules with wildcards to extract needed text based on your needs. For instance, you can create a rule (*) to extract the text between the parentheses, and create a rule @* to extract domains from email addresses. Please do as follows to apply rules to extract text.

Example 1: Extract text between two characters from cells

This section is going to show you how to create a rule to extract text between two characters from selected cells in bulk.

1. Click Kutools > Text > Extract Text to enable the feature.

2. In the Extract Text dialog box, please do the below settings.

2.1) Click the Extract by rule tab;

2.2) In the Range section, click the button to select the range of cells you will extract text from;

2.3) In the Text box, enter the rule you will extract text based on;

Here I want to extract text between parentheses, so I enter (*) into the text box.

2.4) Click the Add button to add the rule to the Rule description box;

2.5) Click OK.

Notes:

3. In the Extract Text dialog box, select a blank cell to output the result, and then click the OK button.

Then texts between parentheses (include the parentheses) are extracted from selected cells in bulk. See screenshot:

Example 2: Extract domain from email address in cells

This section shows you how to create a rule to extract domain from email address in cells.

1. Click Kutools > Text > Extract Text to enable the feature.

2. In the Extract Text dialog box, please do the below settings.

2.1) Click the Extract by rule tab;

2.2) In the Range section, click the button to select the range of cells that contain email address;

2.3) In the Text box, enter the rule @* into the text box.

2.4) Click the Add button to add the rule to the Rule description box;

2.5) Click OK.

Notes:

3. Then an Extract Text dialog box pops up, select a blank cell to place the result, and then click OK.

All email domains are extracted from selected email addresses in bulk.