Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

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:

1) Insert as a formula: Check this 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.
2) If the string or character you entered has duplicates in the same cell, the feature only extracts the text before or after the first occurrence.

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:

1. The wildcard characters ? and * can be used in the rules.
? (question mark): represents any single character. For example, KT? Finds “KTE”, “KTO”, “KTW” and so on;
* (asterisk): represents any number of characters. For example, *east finds “Northeast”, “Southeast” and so on.
2. If multiple rules have been created in the Extract Text dialog box, you can only check the rules that you need to apply and keep others unchecked.

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:

1. The wildcard characters ? and * can be used in the rules.
? (question mark): represents any single character. For example, KT? Finds “KTE”, “KTO”, “KTW” and so on;
* (asterisk): represents any number of characters. For example, *east finds “Northeast”, “Southeast” and so on.
2. If multiple rules have been created in the Extract Text dialog box, you can only check the rules that you need to apply and keep others unchecked.

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.


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    John Rowland · 6 years ago
    the extract text function doesn't work for me. I'm trying to extract specific words such as Ltd and Limited from lists of company names. The function simply loops without extracting the words requested.
  • To post as a guest, your comment is unpublished.
    Ramiz · 6 years ago
    Hi,
    i am using this tools and found this good. But i am having some problem while using Extract Text option in Text Tools. I want to extract tab_cat_id=# (# could be any number 1 or 2 etc) from a URL.
    Can you please guide me on this.