Note: The other languages of the website are Google-translated. Back to English

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

doc extract substring 2

This tutorial shows the methods on extract substring from left, middle or right of a cell, and also explain how to extract text before or after a specific character as below screenshots shown.


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.
doc extract substring 3

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.
doc extract substring 4

Extract n characters form 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.
doc extract substring 5

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.

Before using the Extract Text utility, please take 3 minutes to free install Kutools for Excel: 30-day free trial">Free Download Now!

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

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.
doc extract substring 5

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

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.
doc extract substring 5

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.
doc extract substring 5

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.
doc extract substring 5

Now the substring has been extracted.
doc extract substring 5


Extract substring after or before a specified character

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

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.
doc extract substring 7

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:
doc extract substring 9

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.
doc extract substring 10

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

For directly extract 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.

Before using the Extract Text utility, please take 3 minutes to free install Kutools for Excel: 30-day free trial">Free Download Now!

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

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..
doc extract substring 5

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

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

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.
doc extract substring 5

Now the string before or after the certain character(s) has been extracted.
doc extract substring 5


doc text tools

13 Text tools You Must Have in Excel Which Will Boot Your Efficiency By 90%

▲ Batch edit text string in cells, such as adding same text to cells at once, remove characters at any position and so on.

▲ Except tools displayed in picture, there are 300 advanced tools else in Kutools for Excel, which can solve your 82% Excel puzzles.

▲ Become an Excel expert in 5 minutes, gain people's recognition and promotion.

▲ 110000+ high efficiency people sand 300+ world renowned companies' choice.

30-days free trial, no credit card require


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

Supposing 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.
doc extract substring 18

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.

Before using the Extract Text utility, please take 3 minutes to free install Kutools for Excel: 30-day free trial">Free Download Now!

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

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.

In default, when you select the cell reference which used to extract substring, the cell reference will be absolute which cannot use the auto fill handle to fill formula, please change it to relative.
doc extract substring 5

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

Tip:

If you want to extract strings between two characters (including the two characters), the Extract Text utility of Kutools for Excel also can do you a favor on this operation.

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

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.

Then click Add to add the rule to the Rule description section.
doc extract substring 5

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

Now the string between two certain characters has been extracted.
doc extract substring 5


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.

Before using the Extract Email Address utility, please take 3 minutes to free install Kutools for Excel: 30-day free trial">Free Download Now!

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

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

3. Click OK, the email addresses in each cell have been extracted.
doc extract substring 24


Extract numeric or alphabetical characters from string

If there is a list of data mixed numeric and alphabetical and special characters, you just want to extract the numbers or alphabetical values, you can try Kutools for Excel’s Remove Characters utility.

1. Before you using the Remove Characters utility, you need to have a copy of the data as below screenshot shown:
doc extract substring 25

2. Then select this copy of data, click Kutools > Text > Remove Characters.
doc extract substring 26

3. In the Remove Characters dialog, check Non-numeric option, click Ok.
doc extract substring 27

Now only the numeric characters have been left.
doc extract substring 28

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


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.


  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • 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!
officetab bottom
Comments (36)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
For an example in a cell (G2) > ABC1234568..... > is there.
You just want to have whatever to the right after leaving the left ABC (Fixed). Then Use the formula as =MID(G2,3,200)
Here 3 stands for after 3 character from the left and 200 is an approximate (maximum text number) number that mightbe in the right side . So your job is simple. Thank you
This comment was minimized by the moderator on the site
Thanks for your supplement. (y)
This comment was minimized by the moderator on the site
If you need to grab "everything but" data in another cell. You can use the below formula:


In Excel form:
=Left(A1,FIND("x",A1)-1)

In layman talking it out form:
= everything LEFT of cell data in parentheses (Cell,FIND everything in parentheses ("common unwanted data that must be in quotes", Cell) minus "-1" the number of characters you need removed to the left of the "common unwanted data")



Which would look like this:
USA1234xp25 | USA1234
USA123xp30 | USA123
USA12345xj10 | USA12345

Very Useful when pulling data within a layered naming convention.
This comment was minimized by the moderator on the site
thanks a lot. saved me a lot of time. love you.
This comment was minimized by the moderator on the site
Thank you for this article! the =RIGHT(A2,3) function was exactly what i needed to extract the right 3 numbers into a separate column
This comment was minimized by the moderator on the site
How do extract data out of a string but i only want 2 sets of data in the string?
This comment was minimized by the moderator on the site
Hi, Isgaak, please upload your examples for better understanding.
This comment was minimized by the moderator on the site
Good day can you please help me to add text together into one cell?
This comment was minimized by the moderator on the site
Hi, Berdine, this article introduce several methods to combine text together into one cell, may can help you.

https://www.extendoffice.com/documents/excel/511-merge-rows-keep-data.html
This comment was minimized by the moderator on the site
I need help to separate the address, then city, then state, then zip. See example:


13160 E. 68th st. Broken Arrow, OK 74012
This comment was minimized by the moderator on the site
Hi, Jessica Swaffar, you can read this article https://www.extendoffice.com/documents/excel/2677-excel-extract-state-from-address.html to find the answer.
This comment was minimized by the moderator on the site
I want to calculate the time difference between two times, please Help below is the time format



"Apr, 30 2019 12:44 PM PT" "Apr, 30 2019 12:52 PM PT"
This comment was minimized by the moderator on the site
Hi, Ashfaq, this article https://www.extendoffice.com/documents/excel/1495-excel-calculate-time-difference-between-two-times.html is talking about calculating time difference between two times, maybe can help you.
This comment was minimized by the moderator on the site
There are multiple such entries so want to apply the formula to that
This comment was minimized by the moderator on the site
One T-shirt title Name (NIke) have differents Sizes Like: (XS-2XL) mean XS,S,M,L,XL & 2XL how to Make divide in different rows with mentioned sizes can you help me please
This comment was minimized by the moderator on the site
Hi, Salman, I do not understand your question, could you upload a file to display your data and your desired result?
This comment was minimized by the moderator on the site
I need to separate the city from a cell. Example 15933 Rockingham StFrisco I have a file with several hundred fields with different cities with different character counts. Is there a way to find and separate the city based on it being the all the characters from the right that begins with an uppercase letter? Or any other solution? Thank you
This comment was minimized by the moderator on the site
Hi, Jesse, may be you can try to use the Text to Columns to split the string into columns based on space separator, then remove all columns except the last column that contains the city contents.
This comment was minimized by the moderator on the site
want to separate below mention cell content

60s+2/30s X 60s+16s COTTON 134 x 096 58" DOBBY 100% COTTON NAVY SOFT FINISH
60s X 60s 134 x 096 58" DOBBY 100% COTTON NAVY SOFT FINISH
50LEA X 50LEA 052 X 050 58" PLAIN 100% LINEN ALEXA-101-01 OLIVE SOFT FINISH


to


60s+2/30s X 60s+16s COTTON 134 x 096
60s X 60s 134 x 096
50LEA X 50LEA 052 X 050

in a common formula. Is it possible?
This comment was minimized by the moderator on the site
Hi, Santanu, try formula =LEFT(A1,SEARCH("58", A1)-1). A1 is the cell contains your data.
This comment was minimized by the moderator on the site
Hi, I have a problem to solve which is explained in the following 3 steps:


1. One column is there in which there are over 20,000 text strings which contain random data. Strings can be like: "$300 and above Weather Stations" OR like "Kestrel 5500 Weather Meter - IC-0855".
2. Another column contains 10,000 unique product codes.Codes can be like: "0810-0004
" or "IC-0855"
3. The task is to search every string (in step 1), if there is any of the product codes present in that string, return that product code in front of that respective string. For example from the first two steps, I can see that the string "Kestrel 5500 Weather Meter - IC-0855" contains a product code (IC-0855) mentioned in the column containing product codes, so I want to return that product code in front of this string,

I though this could be solved by VLOOKUP but I couldn't find a way around it. Please help. Thanks in advance.
This comment was minimized by the moderator on the site
Hi, I have a problem to solve and I don't know how to do it.

Here's my data.
10hr 35m
4m 43s

I want to extract it into three columns: hr, m, s
The final result will be like this
hr m s
10 35
4 43

Can I get a formula for the problem?

Thank you
This comment was minimized by the moderator on the site
Need help with this. The data is below

2500: (252,211,183) #FCD3B7 srgb(252,211,183)

want only #FCD3b7 part in another column cell. the characters are not of the same length in the data set
This comment was minimized by the moderator on the site
Hi, AJ, try formula =MID(A1,SEARCH("#", A1),7)
This comment was minimized by the moderator on the site
=RIGHT(B3,LEN(B3)-SEARCH("-", B3))
=LEFT(B10,SEARCH("-", B10)-1)

Can I use both formulas in one cell as I need to extract specific characters from cell ??
Example :
RefBMC 024, INV-006157 - due on 29 Aug 2020
RefHLG14 125, INV-006166 - due on 29 Aug 2020
RefMBR 215, INV-005314 - due on 01 Aug 2020
RefMSC 336, INV-005315 - due on 01 Aug 2020

I need to extract characters after Ref and Before the comma (,) symbol
Can u help me on that ? Thank you,

This comment was minimized by the moderator on the site
Awesome knowledge - thanks so much!
This comment was minimized by the moderator on the site
I have 2 worksheets. I'm looking to find the value in the second sheet but copy the text in the cell above it. ( for example if the value found was in cell B6, I need to copy the value in B5)

This comment was minimized by the moderator on the site
I have a word string "Radhey Shyam Teli, Titirkhi 3 kapilvastu"but i just need the bold part from the string to appear in the next column, mind it i have different counts of letter.
This comment was minimized by the moderator on the site
Tak bohužel nic u uvedeného nefunguje. Chtěl jsem jen použít funkci, aby ze jména a příjmení vypsal pouze příjmení, ale hlásí mi to špatnou syntaxi.
This comment was minimized by the moderator on the site
Hi, if you want to extract first name from full name, this tutorial may help you: https://www.extendoffice.com/documents/excel/829-excel-split-first-last-name.html
There are no comments posted here yet
Load More

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL