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)
433 comments
How do i extrace only lower case characters in a string of excel?
Hello Chloe!
Extract each character from the text individually using MID function.
Convert them to lower case using LOWER function.
Compare the converted lower case character with the original character using EXACT function to determine an exact match.
If the characters are the same, add the character to a new text string created with TEXTJOIN function as described here.
The formula might look something like the following:
=TEXTJOIN("",TRUE, IF(EXACT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1), LOWER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))), MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))
Some text is showing below. I need only "UT" and "PMI" from below mentioned text. Kindly guide.
UT
PMI
Hi! I am not sure I fully understand what you mean.
HOW TO EXECT SPECIFIC WORD LIKE
btag=1ga5cidSSG1261pidvar1var2var3var4var5affid8ti
NEED TO EXECT SSG1261
Hi! To extract multiple characters from text, you can use the MID function. The information you provide does not allow you to give more precise advice.
=VALUE(RIGHT(B4)+VALUE(RIGHT(B5)+VALUE(RIGHT(B6)+VALUE(RIGHT(B7)+VALUE(RIGHT(B8)+VALUE(RIGHT(B9) and so on..
B
4 X0110
5 X1110
6 X1100
7 X1000
8 X1010
9 X1000
10 X1111
11 X1100
Is there an efficient way to do it? some thing like
=sum(value(right(B4):value(right(B20)) (it does not work in excel)
I want to add the last character of the text which is either 1 or 0.
Thank you in advance for the help.
Best regards
Sajjad
Hi! Use the RIGHT function to extract the last digit from all values in the range.
Convert this digit to a number using the VALUE function.
Calculate the sum using the SUMPRODUCT function.
If I understand your task correctly, try the following formula:
=SUMPRODUCT(VALUE(RIGHT(B4:B11,1)))
I have list of below countries and wanted only (HA) and first word from sentence eg. (HA) Cameroon, (HA) United States.
(HA) Cameroon
(HA) Brazil E2B
(HA) Brazil LLP
(HA) Cameroon ViiV
(HA) United States E2B
Is there an easy way to do this with a formula?
Hi! With these guidelines you can extract any word from the text: How to extract word from string in Excel: first, last, Nth, and more. It is not possible to offer you one formula because in the first example you extract two words, in the second example you extract three words.
Hi,
I'm struggling with a formula for a set of lengthy descriptions with a lot of words and numbers. Each description has a reference within it which all start the same, and have either 5, 10, or 15 numbers.
Example:
ABC-12345
ABC-12345-12345
ABC-12345-12345-12345
I'm currently using =LEFT(AF5,FIND(" ",AF5)-1) which works for the ones where the reference is at the left hand side, but sometimes the reference is in the middle of the description.
Can you help at all?
Hi! Without seeing your data, it is difficult to give you advice. Maybe this guide will be helpful: How to extract a word containing a specific character(s).
If this does not help, explain the problem in detail. Provide me with an example of the source data and the expected result.
Hi Alexander,
Thanks for your reply.
Below is an example of how we currently would have the data displayed. The description in column B ,and we are extracting the ABC reference from the description and putting it into it's own reference column A (If we say Reference is Column A, and Description is column B).
Column A Column B
Reference Description
ABC-11464-11321 ABC-11464-11321 - Description of service
ABC-67923-08131 Customer Name / ABC-67923-08131 / Description of Service / Dates of service
ABC-18828-11342-11291 Customer Name Invoice Number ABC-18828-11342-11291 - Month - Service Name
ABC-71296 Ledger Name/H/Coding correction/ Date /ABC-71296 - Contract Title. Email Address
We are currently using the left formula from the first comment if the description is suitable, otherwise we are manually typing it in and there can be thousands of lines to do this to.
The descriptions can come in various text orders but will always have a ABC- reference, but then that ABC reference can either have 5,10 or 15 numbers in the formats I have listed.
So I was just wondering if there is anyway we can extract the ABC reference - regardless of it's length, and regardless of the order of the wording in the description.
Thanks,
Hayley
Hi! I don't know why you didn't use the link to the article I gave you. It covers your case completely.
If I understand your task correctly, try the following formula:
=TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",99)), MAX(1,FIND("ABC", SUBSTITUTE(B1," ",REPT(" ",99)))-50),99))
Hi.
Thank you for your help. I have just adjusted this to make it work in the particular file I needed it for, and it works perfectly.
Appreciate your help.
Hi! I would love some help!
I want to extract text from a cell that identifies the 'team' that is being called out. For example, the output I am looking for would be [Team A;Team B; Team C] from the cell example below:
Team A: XYZ Description
Team B: XYZ Description
Team C: XYZ Description
I am using the =LEFT(A2, SEARCH(":",A2)-1) example, but I only receive [Team A] as the output instead of the [Team A;Team B; Team C] output that I am trying to extract.
Appreciate the help!
Hi! Based on your data, the formula might look something like this:
=LEFT(SUBSTITUTE(SUBSTITUTE(A1,"XYZ Description",""),":",";"),LEN(TRIM(SUBSTITUTE(SUBSTITUTE(A1,"XYZ Description",""),":",";")))-1)
The SUBSTITUTE function replaces unnecessary data with a blank and the ":" character with ";"
Hi sir, i am having A2 below 3 Line , how i will export INC-170 , IAM-860 and INC-180 to another Cell
INC-170: To Classify and Label all Information not handled by Information Systems
INC-180: To Classify and Label all Information not handled by Information Systems
IAM-860: Access Review and Renewals
Hi! If I understood the question correctly, you want to extract the text before the ":" character. Pay attention to the following paragraph of the article above: How to extract text before a specific character. It covers your case completely. For example:
=LEFT(A2, SEARCH(":",A2)-1)
I need the following to be split into 2 or 3 columns if possible, with the text before the "-" in column 1, then the date Monday, January 1, 2024 in the 2nd column, and the bible reference in column 3. The entire row currently contains a reference link, and would like to keep that if possible.
Day 1 - Monday, January 1, 2024 Luke 5:27-39; Genesis 1:1-2:25; Psalm 1
e.g. of split:
Column 1 - "Day 1"
Column 2 - "Monday, January 1, 2024"
Column 3 - "Luke 5:27-39; Genesis 1:1-2:25; Psalm 1"
Hi! Try to use the recommendations described in this article: How to extract word from string in Excel: first, last, Nth, and more.
For example:
=LEFT(A1,SEARCH("-",A1)-1)
=MID(A1,SEARCH("-",A1)+2, SEARCH("#",SUBSTITUTE(A1," ","#",7)) - LEN(LEFT(A1,SEARCH("-",A1)-1))-2)
=MID(SUBSTITUTE(A1," ","#",7), SEARCH("#",SUBSTITUTE(A1," ","#",7))+1, 100)
I hope it’ll be helpful.
Hi There.. I've numerous text stings in below two formats.
I need to get my quantity details i.e. "24011 & 13174" from below text string. What formula would you suggest to get the quantities in both scenarios ?
Thank you so much in advance.
PPT => 29-DEC-2023 00:00:00 => 24011 => LT
SON | GGS | 06-JAN-2024 | Fuel 13174 Liters
Hi! You can use the new CHOOSECOLS and TEXTSPLIT functions to extract the Nth word from text.
=CHOOSECOLS(TEXTSPLIT(A1," "),6)
You can also use the MID function as described in these instructions: How to extract Nth word from a text string
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))), (6-1)*LEN(A1)+1, LEN(A1)))
I'd recommend you to have a look at Split text tool. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
Hi
I have a list of property addresses and post code all in one cell, post codes are separated with a delimiter, I want to extract the post codes into a separate column, I have seen some of the examples in the article above but some of the post codes are different char values i.e. CO2 8TY, E4 6TR
Can I use the RIGHT(SEARCH) function to achieve this?
Thanks
Hi! To advise you on the formula, give an example text string with address and post code and specify what text you want to extract from it.
Hi,
Examples:
45 Marwin Close, Martock,TA126HL
Flats Fro703, Flat 703 Frobisher House, London, Gb,SW1V 3LW
38 Morton Street,CA25UU
72 Fitznael Street,W12 0BB
I want the post codes from the right of the delimiter, i.e. TA126HL
The examples above you can see some of the post codes are different char length
Hi! If I understand your task correctly, the following formula should work for you:
=MID(A1, SEARCH("#", SUBSTITUTE(A1,",","#", LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))+1, 20)
Count the number of "," in the text using the LEN function by removing the "," from the text using the SUBSTITUTE function. Read more: How to count specific characters in a cell.Then replace the last character "," with "#" and determine its position using the SEARCH function. Starting from this position, extract all characters from the text using the MID function.
That worked perfectly, thank you
Hello, I am summarizing all the 10k+ mailer daemons received from my Outlook and need to get only the email addresses (which for sure will be of any number of characters) from this messages:
Your message to 10131967@ YAHOO.COM couldn't be delivered.
Your message to lindavid.me@ gmail.com couldn't be delivered.
Your message to bulanhagui_roseann@ yahoo.com couldn't be delivered.
can anyone help please? Thank you.
Hi! You can extract an email from a text string using regular expressions and the custom RegExpExtract function. You can find the examples and detailed instructions here: How to extract substrings in Excel using regular expressions (Regex). I recommend paying attention to the Regex tool. You can find, extract, compare, delete, or replace strings that match the regular expression pattern you enter. You don't need to install any VBA code. 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.
What formula would break the below list into separate columns like the following example:
A2: R&W 1.5 oz A3: shampoo A4: 200 A5: per case A6: $77.50
B2: R&W 2.7 oz B3: conditioner B4: 200 B5: per case B6: $74.53
R&W 1.5 oz shampoo 200 per case $77.50
R&W 2.7 oz conditioner 200 per case $74.53
R&W 1.5 oz lotion 200 per case $74.55
R&W 1.5 oz body wash 200 per case $74.00
R&W 2 oz shampoo 100 per case $48.00
R&W 2 oz conditioner 100 per case $45.01
R&W 2 oz lotion 100 per case $65.00
Hi! Use the TEXTSPLIT function to split the text into separate text strings. Select the desired text strings using the CHOOSECOLS function. Merge them using the TEXTJOIN function. Here are some example formulas:
=TEXTJOIN(" ",TRUE,CHOOSECOLS(TEXTSPLIT(A2," "),1,2,3))
=CHOOSECOLS(TEXTSPLIT(A2," "),4)
This was so comprehensive and well illustrated! Thank you.
Precisely what I was looking for, thank you!
Exelent