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,
I have a column in column A that has movie names and release dates as one text string. How do separate the movie name from the long date? I've figured out how to extract when it's only the movie name and date together, but sometimes the movie synopsis follows the date in the same column. This is where it stumps me. To split the movie and the date, when it's only those two items, I have a column for each month, and have used =iferror(left(a2,find($f$1,a2)-1),"") where f1 is the heading for January, g2 for February, h3 for March and so on through December. This has successfully given me the movie name, but in the column i have for the date, I have this formula (=text, e2,"mmmm dd, yyyy"). When the synopsis follows the date, I'm left with the date followed by a paragraph of text in that column that I can't seem to get rid of.
Thank you,
Mike
Hello!
Unfortunately, without seeing your data it hard to give you advice.
Please provide me with an example of the source data and the expected result.
It’ll help me understand your request better and find a solution for you.
is there a way to upload a snippet of my worksheet so you can see it? In essence I have one column that I want to split the date from the text. The source column would be formatted like:
Source Result Result
Column A Column B Column C
Movie nameJuly 22, 2020 Extraneous text Movie name July 22, 2020
Movie nameAugust 8,2020 Movie name August 8, 2020
Movie nameSeptember 4, 2020 Alphanumeric Movie name September 4, 2020
Movie nameMay 1, 2021 Text text text text Movie name May 1, 2021
The formatting of my reply should look nothing like that. I want three columns. The first column is for example: The King's ManSeptember 18, 2020 As a collection of history's worst tyrants and criminals....
The second column, I want to output just the movie name
The third column I want to output only the date in long format. I don't want the text that follows the date in the source column kept at all. I want to strip that and toss it away.
I want to put first four alphabets in last in the same column.
For Example want to change 4296HR55U to HR55U4296.
Pls help.
Regards
Hello!
If I understand your task correctly, the following formula should work for you:
=REPLACE(D1,1,4,"")&LEFT(D1,4)
I hope this will help
Hi Anyone can help me out extract "AHU" from "LEGTAIFIYA TVS AHU-02-A-1M"
Hello!
The information presented to you is not enough to give you advice.
Need to determine if "AHU" appears in the text? Or do you need to extract text from some position?
Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.
May I know how to extract 黃惠玲 from string "Wong Wai Ling (黃惠玲)" in cell A1?
Thanks & Best Regards,
Terry
Hello Terry!
If I understand your task correctly, the following formula should work for you:
=MID(A2,FIND("(",A2,1)+1, FIND(")",A2,1) - FIND("(",A2,1)-1)
I hope this will help
I am trying to figure out what formula I would need to use to extract a value from the middle of a string. The length of the value can change so I was looking for a function that allows me to look up between 2 different characters including a character that repeats. An example: What is the area of a 60 degree circle when the bending radius is 1/8 round to the nearest tenth.
I want to extract the 1/8, what formula would I need to use
Hello Norma!
I think that according to your requirements it’s impossible to make a formula. The fact is that in the middle of the line is also the number 60. In addition, the character "/" is not a number.
LEFT(cell, SEARCH("char", cell)-1) is wrong, its ; instead of ,
Hello!
I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? Thank you!
I just have to say Thank you so much for this article!
I want to extract the contents of all cells containing a certain substring delimited by a "," or ";".
For example:
Cell A1 contains "0", Cell B1 contains "index:1", Cell C1 contains "0", Cell D1 contains "index:2"
I want to find all the cells that contain the word "index" and when found, to extract the full contents of the cell into one cell. In the above example, I am looking for a return value index:1,index:2.
How can I do that?
I'm trying to enter the date in one cell (01-Jan-2020) column A and want excel to split it into three columns B,C,D (day, month and year). could you please help me in formula
Dear can somebody help me to extract Q amount from below string,
LON AC YTO Q125 Q100 AC YMQ Q125 AC YYZ
Thanks
Thank you!
i want to extract only LHS or RHS from the text, how to do it
i.e 1234 LHS WERT SERT
1234 RHS WERT SDFR
How do I cut the specific content from a cell?
Hi, I have long texts in the excel cells and I would always need to extract only the Impacted country which differs from cell to cell (in the example below it would be India). Only the text before the country is constant "Impacted Country (For multiple countries select global) = "
Can anybody help how can I do that?
Text in 1 excel cell is for example as below:
---------------------- USER ENTERED DATA ----------------------
Operating System = Windows 10
Impacted Country (For multiple countries select global) = India
Idoc Number: = (NOT ENTERED)
GSAP Transaction Code: = (NOT ENTERED)
EMCS Table = (NOT ENTERED)
Document Number: e.g. BOL, Nom key, LRN# = (NOT ENTERED)
EMCS Dashboard Status = (NOT ENTERED)
Interface ID: = (NOT ENTERED)
Load date/Time = (NOT ENTERED)
Thank you for your help in advance!
How to extract the texts between 3rd and 4th or in case (last - 1) texts between instances of a character
I have a long text (which is a extraction of narration from accounting entries) which contains month and year like "salaries for the month of FEB 2018", "cheque number 123456 paid to Mr. Rajan towards salary for the month of Feb 2018". Now i want to extract the month and year alone. Can somebody help me.
Hi, what should be the formula if I want to extract text from the cell but the result doesn't match the character that I am looking for? the best scenario is as below:
14933 - Cologne West
15116 - Tador East
12345 - Cologne East
45678 - Tador South
36789 - Sucat
15674 - Alabang
From the list, I want to extract only the text but with a condition that if it is not "Tador", just copy the location, otherwise just put "Tador". I have tried several formulas but to no avail.
Hope you can help me out on this, many thanks in advance!
Hello,
I need to know how to extract everything to the left of a string in a list where there is not the same number of characters on the left side or right side of the string in a column. For example, I need to extract everything to the left of " - Garage 1" and " - Store 2" and " - Building" So, I want to get: Supplies-Other, Repairs-Equip, Salaries-Staff. I'm sure this is pretty simple, but I can't figure it out. Thanks in advance for any help anyone can provide!
Supplies-Other - Garage 1
Repairs-Equip - Store 2
Salaries-Staff - Building 3
Hi there:
I am struggling to understand something here. I have the following string "avro_67563908_289(000).csv" to which I apply the following formula: `=MID(A6;SEARCH("_";A6)+1;SEARCH("_";A6;SEARCH("(";A6)+1)-SEARCH("_";A6)-1), which results in a `#VALUE!` error. I have spent quite a bit of time trying to understand this, given that the following test formula works: `=MID(A6;SEARCH("_";A6)+1;SEARCH("(";A6)-SEARCH("_";A6)-1)` - it just starts from the first `_` and not the second which is what I want.
Is there anything obvious that I am doing wrong... the field format is `General`.
Thanks.
-M
Hello all, really need your help guys!
I have a series of columns, for example: Colummn with 3 digit numbers like 420, a column with a word and a number like "Nascar 2", and a column with just a word that says "Honda". I need to make a formula that would combine those 3 columns, but shorten them. So I would use the first number "420", the number from "Nascar 2" and the first letter from "Honda". The solution would be 420-2-H and I need to make a formula for that with the "-" sign in between. Help please!
Hey there:
I would contemplate a formula using a number of different methods:
Step 1 is simple and that would just be: =(A1) (for example)
The second step requires extracting a number from the string and a good reference to achieve this can be found here: https://www.ablebits.com/office-addins-blog/excel-extract-number-from-string/
The third and final step is simple: RIGHT(C1;1) (RIGHT(Cell-Ref;No's chars)
Now that you have your formula sorted out, stitch them together using concatenate:
=Step-1&"-"&Step-2&"-"&Step-3
Replacing Step-1 through Step-3 with your formula. The = sign is used once at the beginning of the formula.
Cheers... -M