How to extract part of text string from cell in Excel?
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.
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.
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.
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.
Note:
If you want to move the extracted results to another location, please copy and paste the extracted results as value firstly.
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.
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:
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.
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.
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
Before using the Extract Text utility, please take 3 minutes to free install Kutools for Excel: Free Download Now!
1. Select the cells that you want to extract characters, click Kutools > Text > Extract Text.
2. In the popping Extract Text dialog, enter *: into Text textbox, then click Add to add this criterion to Extract list.
The wildcard * indicates any length of strings, ? indicate anyone character. In this case, *: represents to extract all characters (including colon mark :) before colon mark from selected cells.
3. Click Ok, a dialog pops out, select a cell to place the extracted text.
4. Click OK, then the substring in each which before colon mark (including colon mark) has been extracted at cell.
Tip
If you want to extract all characters after colon character, type :* into the Text textbox in Extract Text dialog.
If you want to extract only two characters before colon mark, type ??: into the Text textbox in the Extract Text dialog.
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:
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.
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
The Extract Text utility of Kutools for Excel also can do you a favor on this operation.
Before using the Extract Text utility, please take 3 minutes to free install Kutools for Excel: Free Download Now!
1. Select the cells that you want to extract substring between characters, click Kutools > Text > Extract Text.
2. In the Extract Text dialog, enter (*) to the Text textbox, click Add button to add this criterion to the Extract list.
The asterisk mark * indicates any length of string, if you want to extract specific length of character, you can use the question mark ? to indicate anyone character. Here, (*) represents to extract all characters between (), including ().
3. Click Ok to select a cell to place the extracted result, then click OK.
Now the substring between brackets has been extracted.
Unbelievable! A tool changes the way on editing and browsing multiple Office documents.
Open files in multiple windows
Open files in one tabbed window with Office Tab
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: Free Download Now!
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.
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:
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 have been left.
To extract alphabetical values only, check Non-alpha option in the Remove Characters dialog.
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.
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...
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.
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
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.
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
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.
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
