by The tutorial shows how to use the Substring functions in Excel to extract text from a cell, get a substring before or after a specified character, find cells containing part of a string, and more.
Before we start discussing different techniques to manipulate substrings in Excel, let's just take a moment to define the term so that we can begin on the same page. So, what is a substring? Simply, it's part of a text entry. For example, if you type something like "AA-111" in a cell, you'd call it an alphanumeric string, and any part of the string, say "AA", would be a substring.
Although there is no such thing as Substring function in Excel, there exist three Text functions (LEFT, RIGHT, and MID) to extract a substring of a given length. Also, there are FIND and SEARCH functions to get a substring before or after a specific character. And, there are a handful of other functions to perform more complex operations such as extracting numbers from a string, replacing one substring with another, looking up partial text match, etc. Below you will find formula examples to do all this and a lot more.
How to extract substring of a certain length
Microsoft Excel provides three different functions to extract text of a specified length from a cell. Depending on where you want to start extraction, use one of these formulas:
- LEFT function - to extract a substring from the left.
- RIGHT function - to extract text from the right.
- MID function - to extract a substring from the middle of a text string, starting at the point you specify.
As is the case with other formulas, Excel substring functions are best to learn from an example, so let's look at a few ones.
Extract substring from start of string (LEFT)
To extract text from the left of a string, you use the Excel LEFT function:
Where text is the address of the cell containing the source string, and num_chars is the number of characters you want to extract.
For example, to get the first 4 characters from the beginning of a text string, use this formula:
=LEFT(A2,4)
Get substring from end of string (RIGHT)
To get a substring from the right part of a text string, go with the Excel RIGHT function:
For instance, to get the last 4 characters from the end of a string, use this formula:
=RIGHT(A2,4)
Extract text from middle of string (MID)
If you are looking to extract a substring starting in the middle of a string, at the position you specify, then MID is the function you can rely on.
Compared to the other two Text functions, MID has a slightly different syntax:
Aside from text (the original text string) and num_chars (the number of characters to extract), you also indicate start_num (the starting point).
In our sample data set, to get three characters from the middle of a string beginning with the 6th character, you use the following formula:
=MID(A2,6,3)
Tip. The output of the Right, Left and Mid formulas is always text, even when you are extracting a number from a text string. If you want to operate on the result as a number, then wrap your formula in the VALUE function like this:
=VALUE(MID(A2,6,3))
Extract substring before or after a given character
As shown in the above examples, the Left, Right and Mid functions cope nicely with uniform strings. When you are dealing with text strings of variable length, more complex manipulations shall be needed.
Note. In all of the below examples, we will be using the case-insensitive SEARCH function to get the position of a character. If you want a case-sensitive formula, use the FIND function instead.
How to extract text before a specific character
To get a substring preceding a given character, two things are to be done: first, you determine the position of the character of interest, and then you pull all characters before it. More precisely, you use the SEARCH function to find the position of the character, and subtract 1 from the result, because you don't want to include the character itself in the output. And then, you send the returned number directly to the num_chars argument of the LEFT function:
For example, to extract a substring before the hyphen character (-) from cell A2, use this formula:
=LEFT(A2, SEARCH("-",A2)-1)
No matter how many characters your Excel string contains, the formula only extracts text before the first hyphen:
How to extract text after character
To get text following a specific character, you use a slightly different approach: get the position of the character with either SEARCH or FIND, subtract that number from the total string length returned by the LEN function, and extract that many characters from the end of the string.
In our example, we'd use the following formula to extract a substring after the first hyphen:
=RIGHT(A2,LEN(A2)-SEARCH("-",A2))
How to extract text between two instances of a character
To get a substring between two occurrences of a certain character, use the following generic formula:
The first two arguments of this MID formula are crystal clear:
Text is the cell containing the original text string.
Start_num (starting point) - a simple SEARCH formula returns the position of the desired character, to which you add 1 because you want to start extraction with the next character.
Num_chars (number of chars to extract) is the trickiest part:
- First, you work out the position of the second occurrence of the character by nesting one Search function within another.
- After that, you subtract the position of the 1st occurrence from the position of the 2nd occurrence, and subtract 1 from the result since you don't want to include the delimiter character in the resulting substring.
For example, to extract text surrounded by two hyphens, you'd use this formula:
=MID(A2, SEARCH("-",A2) + 1, SEARCH("-",A2,SEARCH("-",A2)+1) - SEARCH("-",A2) - 1)
The screenshot below shows the result:
If you are looking to extract text between 2nd and 3rd or 3nd and 4th occurrences of the same character, you can use a more compact SEARCH SUBSTITUTE combination to get the character's position, as explained in How to find Nth occurrence of a character in a string:
In our case, we could extract a substring between the 2nd and 3rd hyphens with the following formula:
=MID(A2, FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2))+1, FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3)) - FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2))-1)
How to find substring in Excel
In situations when you don't want to extract a substring and only want to find cells containing it, you use the SEARCH or FIND function as shown in the above examples, but perform the search within the ISNUMBER function. If a cell contains the substring, the Search function returns the position of the first character, and as long as ISNUMBER gets any number, it returns TRUE. If the substring is not found, the search results in an error, forcing ISNUMBER to return FALSE.
Supposing, you have a list of British postcodes in column A and you want to find those that contain the substring "1ZZ". To have it done, use this formula:
=ISNUMBER(SEARCH("1zz", A2))
The results will look something similar to this:
If you'd like to return your own message instead of the logical values of TRUE and FALSE, nest the above formula into the IF function:
=IF(ISNUMBER(SEARCH("1zz", A2)), "Yes", "")
If a cell contains the substring, the formula returns "Yes", an empty string ("") otherwise:
As you may remember, the Excel SEARCH function is case-insensitive, so you use it when the character case does not matter. To get your formula to distinguish the uppercase and lowercase characters, opt for the case-sensitive FIND function.
For more information on how to find text and numbers in Excel, please see If cell contains formula examples.
How to extract text from cell with Ultimate Suite for Excel
As you have just seen, Microsoft Excel provides an array of different functions to work with text strings. In case you are unsure which function is best suited for your needs, commit the job to our Ultimate Suite for Excel. With these tools in your Excel's arsenal, you just go to Ablebits Data tab > Text group, and click Extract:
Now, you select the source cells, and whatever complex strings they contain, a substring extraction boils down to these two simple actions:
- Specify how many characters you want to get from the start, end or middle of the string; or choose to extract all text before or after a given character.
- Click Insert Results. Done!
For example, to pull the domain names from the list of email addresses, you select the All after text radio button and type @ in the box next to it. To extract the user names, you select the All before text radio button, as shown in the screenshot below.
And you will get the following results in a moment:
Apart from speed and simplicity, the Extract Text tool has extra value - it will help you learn Excel formulas in general and substring functions in particular. How? By selecting the Insert as formula checkbox at the bottom of the pane, you ensure that the results are output as formulas, not values.
In this example, if you select cells B2 and C2, you will see the following formulas, respectively:
- To extract username:
=IFERROR(LEFT(A2,SEARCH("@",A2)-1),"") - To extract domain:
=IFERROR(RIGHT(A2, LEN(A2)- SEARCH("@",A2) - LEN("@") + 1),"")
How much time would it take you to figure out these formulas on your own? ;)
Since the results are formulas, the extracted substrings will update automatically as soon as any changes are made to the original strings. When new entries are added to your data set, you can copy the formulas to other cells as usual, without having to run the Extract Text tool anew.
If you are curious to try this as well as many other useful features included with Ultimate Suite for Excel, you are welcome to download evaluation version.
More formulas for substrings in Excel
In this tutorial, we have demonstrated some classic Excel formulas to extract text from string. As you understand, there can be almost infinite variations of these basic scenarios. Below you will find a few more formula examples where the Text functions come in handy.
Available downloads
Excel substring functions - practice workbook (.xlsx file)
Ultimate Suite - trial version (.exe file)
441 comments
Hello
if i cells with:
111222
11222
1222
and i want extract "1" from them like this:
111
11
1
how i can do it?
Hello!
Use substring functions to extract text from cell:
=LEFT(A1,SEARCH(2,A1)-1)
Hope this is what you need.
Hi can you kindly help me, i have a situation where in a cell there is a few items that i need to separate out into different columns
"Item 1. D/W RSC (CTN 35)
Material Code: -
Quantity: 1,000 piece
Account Name (GL): OPS PACKING MATERIALS (4340000)
BusA/CC: AM71/AM2D
Mandatory to Quote: Yes
Item 2. D/W CTN (CTN 90)
Material Code: -
Quantity: 1,000 piece
Account Name (GL): OPS PACKING MATERIALS (4340000)
BusA/CC: AM71/AM2D
Mandatory to Quote: Yes
Item 3. D/W CTN (CTN 98)
Material Code: -
Quantity: 2,000 piece
Account Name (GL): OPS PACKING MATERIALS (4340000)
BusA/CC: AM71/AM2D
Mandatory to Quote: Yes
Item 4. D/W RSC (CTN 56)
Material Code: -
Quantity: 1,000 piece
Account Name (GL): OPS PACKING MATERIALS (4340000)
BusA/CC: AM71/AM2D
Mandatory to Quote: Yes
Item 5. S/W RSC (CTN 28)
Material Code: -
Quantity: 1,000 piece
Account Name (GL): OPS PACKING MATERIALS (4340000)
BusA/CC: AM71/AM2D
Mandatory to Quote: Yes
( This is all in 1 cell)
How do I separate them
I'm in desperate need, please help me, thank you
Hello!
If you want to split text into cells by line break, you can use the Excel tool - Text to columns. Use the key combination CTRL+J to specify line feed as "other" character.
You can also use the Split Text tool. It provides many options to split text into cells. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
is there any formulas that i can use?
Hello!
we have columns of data in each - mention below.
Please help us to segregate M followed by 8 digits in a separate cell.
-------------------------------------------------------------
"3 laptops
Po# m17121848 item # 9999207718877
Po# m18147289 item # 9999207707199
Po# m34283154 item # 9999207718875"
-------------------------------------------------------------
"p.o m31153508
item number 9999207495586"
-------------------------------------------------------------
"p.o. m355515737
item number 9999207564615"
-------------------------------------------------------------
"p.o m31153508
item number 9999207495586"
------------------------------------------------------------
"p.o m31153508
item number 9999207495586"
-------------------------------------------------------------
Hello!
To extract part of the text from a string, use the MID function:
=MID(A2,SEARCH("m",A2),9)
I hope it’ll be helpful.
Thanks for the response. The above formula is working for Single M in single cell but we have more of multiple M************** in single excel cell. can you please us to provide mulitple M formula.
For example:
Below data available in single excel cell.
Po# m17121848 item # 9999207718877
m18147289 item # 9999207707199
Pom34283154 item # 9999207718875"
Hi!
Specify exactly what results from your data you want to get.
i want to get M followed by 8 digit. i have multiple occurrence Mxxxxxxx in a single cell data. on this formula (=MID(A2,SEARCH("m",A2),9)) work for single occurrence of M followed by 8 digits please help me to get all other occurrence of M.
we want to extract multiple occurrence of M with 8 digits only in single cell data. for example
we have data in a single cell of below
Po# m17121848 item # 9999207718877
m18147289 item # 9999207707199
Pom34283154 item # 9999207718875"
we want to get in a cell like below:
M17121848
M18147289
M34283154
Thanks for your help.
Hi!
Use regular expressions to solve your problem.
Use a custom function RegExpExtract:
=RegExpExtract(A1,"m\d+")
You can find the examples and detailed instructions here: How to extract substrings in Excel using regular expressions (Regex).
I hope I answered your question. If something is still unclear, please feel free to ask.
Hi,
I have this data and I need to extract only the characters under the format M9xxxxxxxxxx.
M90000000001; 1062172; 4503260578
M90000000002; L20000000001; M90000000005
M90000000003
I am using the formula: =MID(A2,FIND("M9",A2,1),12) but this does not bring all the data, in case I have 2 values in a single cell M9xxxxxxxxxx (i.e. line 2).
Is there a way to extract both?
Thank you!
Hello!
Separate your long text values into individual cells using semicolons as delimiters. See detailed instructions here: How to split cells in Excel: Text to Columns, Flash Fill and formulas.
After that, you can use your formula.
Thank you for the suggestion!
Hello!
I have data in this format under excel sheet.
I only want to extract characters from the below column, how do I leave/remove special characters (unwanted symbols) and only extract characters which are present in each row.
Please help me..
location
Yakima, WA
Distrito Federal, México
Prison
?
laugh of january
Karachi, Pakistan
kiwook. ♡
grace, she/her, 24
QATAR
eve ♡ [swe/eng/한]
Bengaluru, India
Saudi Arabia .Taif
kiwook. ♡
Lab of Womb
Nederland
Calabria, Italia
Iraq
Alexandria/Tanta
#everywhere
Ruwa Zimbabwe
Reality
Ayodhya
Maldives
??
Szczecin, Polska
Hargeisa, Somaliland
Prosthodontics Section, CoD
Davao City, Davao Region
under these bitches’ skin
Islamabad, Pakistan
??
khandwa mp
New York, NY ✈️ Houston, TX
United Arab Emirates
Atlantis
Lampung, Indonesia
08/30/17
Jakarta Pusat, DKI Jakarta
losers club
tyler williams inc
Kharkhoda, India
Iraq
Tweets are my own and not representative of my employer
Philippines
Jeddah - khartoum
Madinah
Hello!
To remove special characters from text, you can use regular expressions. You can find the examples and detailed instructions here: Excel Regex to remove certain characters or text from strings.
You can use this formula:
=TRIM(RegExpReplace(A5, "[^0-9a-zA-Z\.,/ ]+", ""))
You can also use Regex Tools for Excel. With Regex Tools, which are part of Ultimate Suite for Excel, you can find, extract, remove, or replace strings that match a pattern.
Hello, I have a task to extract a specific location code from a string. Examples from the text I am using are as follows:
AUS177-4M
Canada551-3W
MEX316-3W
US160-3Mo
ARG265-2W
MEX363-5Mo
US351-4W
GER195-6Mo
GER529-2Mo
AUS301-7Mo
GER60-3W
ENG102-8Mo
AUS219-9W
ENG342-10Mo
US476-11M
GER93-6W
GER442-10M
Japan17-8W
Canada559-11Mo
ARG389-11Mo
Canada121-12M
As you can see the data aren't neatly arranged, nor are they all a set amount of characters long. I need to be able to extract the location text (Canada, GER, US, Japan) exactly as it appears in the text string and display it in another column. Is there any way you could assist me in this?
Hello!
To extract all characters up to the first digit in the text, try using the formula
=LEFT(A2,MIN(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A2)), "",FIND({1;2;3;4;5;6;7;8;9;0},A2)))-1)
Hope this is what you need.
If I just wanted to display the number or letter(s) at the end of a string, would this approach work also? For example, If I had HSM-11Mo, and I wanted to display just the 11 or the Mo inside of a column by itself, would I be able to with this type of formula? I am not an excel expert by any means so I apologize for any confusion in my statement.
Hello!
If you want to show all characters after the last digit, then try this formula:
=MID(A2,LOOKUP(,-MID(A2,ROW($1:$99),1),ROW($1:$99))+1,50)
This formula works on most of the cells in my data set but a few of them still include the dash and number. For example, the data
NGE270-18M
SUA110-5M
EXM390-18Mo
NGE430-17W
would all return a -18M or -18Mo or -17W depending on the cell. Is there anyway I can fix this?
Hi!
Read carefully this paragraph and the example above: How to extract text after character.
This is the answer to your question.
I have data in this format under excel sheet.
S02E01.the.Wild.Goose.Chase
S02E02.Needle.in.a.Haystack
S02E03.Might.of.the.Atwal.Empire
S02E04.True.Lies
S02E05.Wedding.Bells.Make.a.Loud.Sound
S02E06.Revenge.Is.Best.Served.Cold
S02E07.the.Girl.and.the.Cop
S02E08.Goons.Guns.and.Bombs
S02E09.the.Hunter.Is.Now.the.Hunted
S02E10.Thats.the.Way.the.Cookie.Crumbles
I want result as any data excluding starting 7 characters.
Thanks
Hello!
To extract a portion of text starting at a specific position, use the MID function -
=MID(A1,8,50)
Hello
I would appreciate any help. I have a high volume of the below data (located in one cell);
"Winning Combination: 2/1/1/1/1/2/1/1/3/1
Status: Official
Results: (9/10)
Winners: 2015.43
Dividend: R42.60
Results: (10/10)
Winners: 141.00
Dividend: R2,439.80"
I need to extract;
2015.43
42.60
141
2439.80
Any advice?
Thanks
Hello!
To extract numbers from text, you can use regular expressions. You can find detailed instructions and examples in this article: How to extract substrings in Excel using regular expressions (Regex).
Hi there. I have a column with notes where I want to extract the 15 digits that appear after IRCT (or irct), including the IRCT into an adjacent (blank) column. The IRCT number can appear anywhere in the cell.
E.g. (2022-02-09 16:24:37)(Select): irct2012042495523N1;
or IRCT2017011520145N4; (2021-09-01 15:31:36)(Screen): #66 Abdollahi 2019 might be linked & has abstract
Are you able to help? I can only find instructions for extracting a) after a single character (not a string) and no instructions for specifying the length of the string to be extracted.
Thank you!!
Hello!
Please use the following formula -
=MID(A1,SEARCH("irct",A1),19)
The article above has all the information you need. I hope it’ll be helpful.
Thank you for this!! I was looking for something similar.
Brilliant! Thank you so much for the quick response - much appreciated :-). One final question, if the cell doesn't contain "irct", what is the argument for returning a 0, rather than #VALUE!)
Hello!
To replace an error message with a value, use the IFERROR function.
You can use this formula:
=IFERROR(MID(A1,SEARCH("irct",A1),19),0)
How Can I select sugar and flax from this formula
"Milk, sugar, Vegetable shortening, canola oil, milk powder, cardamom essence, whole wheat flour, flax seed, raisin, sugar, almond, cashew"
what formular can i use to extract only characters in a cell without the LAST 4 DIGITS
e.g "Vitamin A supplementation 6-11 months 2019" results should be "Vitamin A supplementation 6-11 months"
and
"LLIN given to pregnant women 2021" results should be "LLIN given to pregnant women"
Hello!
Extract all characters from the text, except the last four. Use the LEFT and LEN functions.
=LEFT(A1,LEN(A1)-4)
I hope I answered your question.
it worked!!, thanks
Hi,
I have cells containing this pattern:
A.BBBBB
A.B.CCCCCCC
A.B.C.DD
How do I get the substring to the right of the utmost right period, e.g. BBBBB, CCCCCCC, DD?
TIA!
Hello!
To extract the text at the end of a string, use regular expressions as described in this tutorial: How to extract substrings in Excel using regular expressions (Regex).
Pattern: \w+$
This should solve your task.
Thanks Alexander, this helps a lot, though not completely, since some of the strings look like A.B.C.DD-EEE and it's DD-EEE that I'd like to extract.
Hello!
To get all characters after the last dot, use regular expression
'([^\..]+)$
You're super. Thanks a lot!
Hey, How can I extract from cell containing 4c,5c,9e,10z,12c the words containing c like, the extraction of above should look like- 4c,5c,12
Hello!
You need to extract the text according to the pattern. We have a special tutorial on this. Please see – Regex to extract strings in Excel (one or all matches)
Use a search pattern "\d+c"
Try the following formula:
=TEXTJOIN(",",,RegExpExtract(A1, "\d+c"))
Thanks, But is there anyway of implementing this into Google Sheets?
Hello Siddhant,
RegExpExtract doesn't work in Google Sheets but you can try this formula instead:
=ArrayFormula(TEXTJOIN(",",TRUE,IFNA(REGEXEXTRACT(SPLIT(A1,","),"\d+c"))))
Olá,
Eu gostaria de extrair para outra coluna o 3880-109 Ovar do seguinte texto; Zona Industrial de Ovar, Loja n.º L 00.013, Av. Dom Manuel I, 3880-109 Ovar.
Como faço isso?
Obrigado
Hello!
I believe the following formula will help you solve your task:
=MID(A1,SEARCH("#", SUBSTITUTE(A1,",","#",LEN(A1)- LEN(SUBSTITUTE(A1,",",""))))+2,50)
Obrigado Alexander Trifuntov pela pronta ajuda, infelizmente meu excel é de 2007 em português e a fórmula dá erro.
Hello!
To translate Excel functions to another language, try using Excel Functions Translator.
continua a dar erro na fórmula
Hi!
Perhaps you do not use a comma, but a semicolon as a separator in the formula. It depends on the regional settings of your Windows.
Hello!
I'd like to extract a string of text that occurs between the first "_" and the 5th "_" from the right (after "ztt_" and before "_dev_rev_vX_icr_tt", where X is a changing version number)
For example -
Cell: ztt_tool_vacuum01_dev_rev_v3_icr_tt
Extract: tool_vacuum01
Another example -
Cell: ztt_first_mom_hair01_col01_dev_rev_v9_icr_tt
Extract: first_mom_hair01_col01
Another example -
Cell: ztt_mop_def01_col01_dev_rev_v4_icr_tt
Extract: mop_def01_col01
Could you help? Thank you :)
Hello!
Please check the formula below, it should work for you:
=MID(A1,SEARCH("_",A1,1)+1,SEARCH("_dev_rev",A1,1)-SEARCH("_",A1,1)-1)
Hope this is what you need.
This worked wonders! Thank you kindly :)
HI,
Thanks a lot for your attention and reply,
If you suggest different formulas for different patterns.
I will be very thankful to you.
Hi,
Very nice article.
I want to get the result following text string:-
P08LREMTNM172// 10.139.131.69-LTS-MTN-MSAG25CANALBANK2-A-M result is canal bank
P08LREFZDM090//Neshaman Park Awan Market Ferozpur Road 10.139.97.146 result is neshaman park
P08LREGBGM101//C-51 Hafeez center Gulberg 10.139.82.198 result is hafeez center
P08LREARDM064//10.139.130.166-LTS-ARD-C15BTYPEFLAT-A-M result is c15bty
P08LREMTNM065//10.139.131.14-LTS-MTN-065HanjarwalChowk-A-M result is Hanjarwalchowk
P08LREGNRM025//10.139.114.30-LTS-GRI-C2160feetRoad-A-M result is c2160feetroad
P08LREGNRM018//10.139.114.130-LTS-GRI-C19JaffriaColony-A-M result isc19jaffriacolony
P08LREMALM054//10.139.64.86-LT-LHR-MSAG14sunderIndustrialstateMAL-Z-M result issunderindustrial estate
P08LREASLM050//MSAG-1 Central Park FZRD 10.139.47.110 result is central park
P08LREFZDM024//10.139.115.14-LTS-FZR-C25niaziachkFZR-A-M result isniaziachk
P08LREFZRM085//MSAG-51 -Qanchi Main Bazar near Batul Islam Madrassa- FZR 10.139.97.126 result is Qainchi main bazar
P08LREJTNM020//C-29 Near Bank Lalazar Colony Phase-II (Riawind Road) Lahore -10.139.78.134
P08LREGNRM017//10.139.114.98-LTS-GRI-B4ChubarjiparkGRI-A-M
P08LREMRDM008//C-35 Near Ilyas Autos Saidpur Multan Road -10.139.77.158
and so on
I will be very appreciate your great help..
Thanks,
Hi!
To write a formula to extract a string from text, your data must have a common pattern and be consistent. I don't see it here.
superb explanation !...
enjoyed throughout reading & understanding this article . maintain this easiness in every complex things. specially with illustration out of the box
Thanks really it
helpful for me
keep going guys
it
use a smart functions
thanks
Hello!
I want to extract the date from this text message:
Overdue for unfinished orders as of: 16-11-2019
Hello!
I recommend reading this guide: Convert text to date and number to date.
Try this formula:
=DATEVALUE(RIGHT(A1,10))