Extract substring from text string in Excel
It may be a common task for you that you need to extract substrings from text strings, in Excel, there is not a direct function for doing this, but, with the help of the LEFT, RIGHT, MID and SEARCH functions, you can extract kinds of substrings as you need.
- Extract substring with specific number of characters from text string
- Extract substring before or after a specific character from text string
Extract substring with specific number of characters from text string
In Excel, the LEFT, RIGHT and MID functions can help you to extract the substring of a specified length from left, right or middle of the text string.
- LEFT function: to extract a substring from the left of the text string.
- RIGHT function: to extract text from the right of the text string.
- MID function: to extract a substring from the middle of a text string.
Extract the substring from the left of the text string:
The LEFT function can help you to extract the first x characters from the text string, the generic syntax is:
- text: the text string that you want to extract characters.
- num_chars: the number of characters that you want to extract from the left side of the text string.
Please enter the following formula into a blank cell:
Then, drag the fill handle down to the cells that you want to apply this formula, and all first 3 characters have been extracted from the original text, see screenshot:
Extract the substring from the right of the text string:
To extract the substrings from the end of the text, please use the RIGHT function:
- text: the text string that you want to extract characters.
- num_chars: the number of characters that you want to extract from the right side of the text string.
For example, to extract the last 6 characters from the text string, please enter the below formula into a blank cell:
And then, copy this formula to other cells that you need, and all 6 characters have been extracted from the right of the text, see screenshot:
Extract the substring from the middle of the text string:
To extract the substring with a specific character length from the middle of the text, the MID function can do you a favor.
- text: the text string that you want to extract characters.
- start_num: the location of the first character that you want to extract.
- num_chars: the total number of characters you want to extract.
For example, to extract 3 characters which begins at the seventh character from the text string, please enter the below formula into a blank cell:
And then, drag the fill handle down to the cells to apply this formula as you need, you will get the following result:
Extract substring before or after a specific character from text string
The LEFT, RIGHT and MID functions can help you to extract the uniform substring with specific number of characters, but, sometimes, you may need to extract substring of variable length, you can create some more complex formulas to solve this job.
Extract substring before a specific character
For extracting all characters before a specific character, the generic syntax is:
- text: the text string that you want to extract characters from.
- char: the specific character to extract the substring based on.
For instance, I want to extract all characters that before the hyphen character, please apply the following formula into a blank cell:
Then drag the fill handle down to the cells that you want to apply this formula, and all characters before the hyphen have been extracted as following screenshot shown:
Explanation of the formula:
- SEARCH("-",A2)-1: This SEARCH function is used to find the position of the hyphen character in cell A2, and subtract 1 to exclude the character itself. It is recognized as the num_chars argument of the LEFT function.
- LEFT(A2, SEARCH("-",A2)-1): This LEFT function extracts the number of characters returned by the SEARCH function from the left side of the text in cell A2.
Extract substring after a specific character
If you want to extract the substrings after a specific character, the RIGHT, LEN, SEARCH function can help you, the generic syntax is:
- text: the text string that you want to extract characters from.
- char: the specific character to extract the substring based on.
To extract the characters after the hyphen character, please apply the below formula:
And then, copy this formula to the cells that you want to use, and you will get the result as below screenshot shown:
Explanation of the formula:
- SEARCH("-",A2): This SEARCH function is used to find the position of the hyphen character in cell A2.
- LEN(A2)-SEARCH("-",A2): The total length of the text string returned by LEN function subtracts the number returned by SEARCH function to get the number of the characters after the specific character. This is recognized as the num_chars argument of the RIGHT function.
- RINGT(): This RIGHT function is used to extract the number of characters from the end of the text string in cell A2.
Extract substring between two specific characters
If you need to extract the substring between two given characters, maybe the below functions can help you, the generic syntax is:
- text: the text string that you want to extract characters from.
- char: the specific character to extract the substring based on.
For example, to extract the text between the two hyphens, please use this formula:
Then, drag the fill handle down to apply this formula to other cells, and you will get the result as following screenshot shown:
Explanation of the formula:
- SEARCH("-",A2) + 1: The SEARCH function is used to return the position of the hyphen character, and add 1 means to extract the substring from the next character. It is recognized as the start_num argument of the MID function.
- SEARCH("-",A2,SEARCH("-",A2)+1): This part of the formula is used to get the position of the second occurrence of the hyphen character.
- SEARCH("-",A2,SEARCH("-",A2)+1) - SEARCH("-",A2) - 1): Use the position of the second hyphen character subtract the position of the first hyphen character to get the number of the characters between these two hyphen characters, then subtract 1 from the result to exclude the delimiter character. This is recognized as the num_chars argument of the MID function.
- MID(): This MID function is used to extract the substring based on the above two arguments.
Relative functions used:
- LEFT:
- The LEFT function extracts string from left of the text string.
- RIGHT:
- The RIGHT function returns the text from right of the text string.
- MID:
- The MID function returns the specific characters from the middle of text string.
- SEARCH:
- The SEARCH function can help you to find the position of a specific character or substring from the given text
More articles:
- Get Or Extract The First Word From Text String In Excel
- To extract all the first words from a list of text strings which are separated by spaces, the LEFT and FIND function in Excel can do you a favor.
- Get Or Extract The Last Word From Text String In Excel
- To extract the last word from text string which are separated by the space characters, normally, you can create a formula based on the TRIM, SUBSTITUTE, RIGHT and REPT functions in Excel.
- Extract Last Line Of Text From A Multi-Line Cell
- To extract the last line of text from a text string which is separated by line breaks, normally, there is no direct way for you to solve this. This article, I will introduce a formula to deal with this task in Excel.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
