Skip to main content

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

Author: Sun Last Modified: 2024-11-26

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.

A screenshot demonstrating extracting parts of text strings in Excel

Extract substring from left, mid or right

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

=LEFT(B3,3)

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.
A screenshot showing the result of extracting first three characters using the LEFT formula in Excel

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:

=RIGHT(B9,6)

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.
A screenshot displaying the result of extracting the last six characters using the RIGHT formula in Excel

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:

=MID(B15,4,3)

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.
A screenshot showing the output of extracting characters from the middle of a string using the MID formula in Excel

Note: If you want to move the extracted results to another location, please copy and paste the extracted results as value firstly.

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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select the cells that you want to extract substrings from, click Kutools > Text > Extract Text.
A screenshot of the Extract Text option on the Kutools tab in Excel

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.
A screenshot of the Extract Text dialog in Kutools showing substring extraction options

The first N character: extract substring from left. For instance, extract first 2 characters, check this option and type 2 into textbox.
A screenshot of the first N characters extraction option in Kutools

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.
A screenshot of the last N characters extraction option in Kutools

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.
A screenshot of the middle N characters extraction option in Kutools

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.
A screenshot of selecting a destination cell for extracted substrings

Now the substring has been extracted.
A screenshot of extracted substrings displayed in the worksheet after using Kutools

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


Extract substring after or before a specified character

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:

=RIGHT(B3,LEN(B3)-SEARCH("-", B3))

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.
A screenshot showing extracted substrings after a specified character using the RIGHT formula in Excel

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

=LEFT(B10,SEARCH("-", B10)-1)

Example result is shown as below:
A screenshot showing extracted substrings before a specified character using the LEFT formula in Excel

Note:

The data may be lost or changed while you copy and paste the formula results to another location. For preventing this problem from happening, you can copy and paste the formula results as value after applying the formula. Or you can try the Method B.
A screenshot showing the pasted as value option in Excel

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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select the cells that you want to extract characters, click Kutools > Text > Extract Text.
A screenshot of the Extract Text option on the Kutools tab in Excel

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..
A screenshot of the Extract Text dialog for specifying text extraction before or after characters

Before the text: extract substrings before the entered character(s). For instance, type – into the textbox, all characters before – will be extracted.
A screenshot showing the option to extract text before a specific character

After the text: extract substrings after the entered character(s). For instance, type – into the textbox, all characters after – will be extracted.
A screenshot showing the option to extract text after a specific character

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.
A screenshot of selecting a cell to display extracted substrings

Now the string before or after the certain character(s) has been extracted.
A screenshot of extracted substrings displayed after using the Kutools

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


Extract substring between two characters

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:

=MID(LEFT(B3,FIND(")",B3)-1),FIND("(",B3)+1,LEN(B3))

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.
A screenshot showing extracted text between two characters using the MID formula in Excel

Note: If the formula is a little difficult for you, you can try the Method B, which is using a handy tool to quickly solve this problem.

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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

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

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.
A screenshot showing the Arguments input section for extracting text between two characters in the Formula Helper dialog

3. Click Ok, now the first result has been gotten, then drag auto fill handle over cells that need this formula.
A screenshot of the extracted text between two characters using Kutools

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now

Tip: If you want to extract strings between two characters (including the two characters), the Extract Text utility of Kutools for Excel also can help. Please do as follows:

1. Select the cells that you want to extract substring between characters, click Kutools > Text > Extract Text.
A screenshot of the Extract Text option on the Kutools tab in Excel

2. In the popping Extract Text dialog, under Extract by rule tab, go to the Text section, type the characters that you want to extract string between, and the string can be repented by the wildcard *. If you want to extract string with fixed length, the wildcard ? can be used, one ? indicate one character.

Click Add to include the rule in the Rule description section.
A screenshot showing how to add a rule for extracting text between two characters in the Extract Text dialog

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

The string between two specified characters is now extracted.
A screenshot showing the extracted text between two characters using Kutools


Extract E-mail address from a string

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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

1. Select the cells that will be extracted email address, then click Kutools > Text > Extract Email Address.
A screenshot of the Extract Email Address option in Kutools tab in Excel

2. Then a dialog pops out for you selecting a cell to output the address emails.
A screenshot showing the dialog for selecting a cell to output extracted email addresses

3. Click OK, the email addresses in each cell have been extracted.
A screenshot of extracted email addresses in Excel using Kutools

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


Extract numeric or alphabetical characters from string

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.
A screenshot of the original data before using the Remove Characters utility

2. Then select this copy of data, click Kutools > Text > Remove Characters.
A screenshot showing the Remove Characters option in Kutools tab in Excel

3. In the Remove Characters dialog, check Non-numeric option, click Ok.
A screenshot of the Remove Characters dialog with the Non-numeric option selected

Now, only the numeric characters remain.
A screenshot showing the remaining numeric characters after using the Remove Characters utility

To extract alphabetical values only, check Non-alpha option in the Remove Characters dialog.
A screenshot showing the remaining alphabetical characters after using the Remove Characters utility

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


Download sample file

doc sampleClick to download this sample file


Other Operations (Articles) Related To File Conversion

Extract time from datetime string
Provides tricks to extract time (hh:mm:ss) or hour/minute/second only from datetime string (mm/dd/yyyy hh:mm:ss)

Extract rows that meet criteria
In this article, it can help you quickly extract these rows that meet criteria to another location in Excel except finding and copying them one by one.

Extract a nth character from string
Here will introduce the methods on extracting the nth character from a string, for example, extract the 3th character from string a1b2c3, the result is b.

Extract substring between two characters
Show the methods on extracting substring between two same or different characters.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!