Extract Text From String
If you need to extract text from string in Excel with a known character, there is an easy way of doing this with just one formula. All you need is to know how to use 2 basic text functions.
Here are the 2 functions we are going to use to extract text from string with a known character:
- We are going to use SEARCH Function to determine the position of the known character.
- We are going to use MID Function to fetch out text from target text.
SEARCH Function returns position of a character in a text (searching from left to right).
Assuming Cell A1 has the value “20W13W49”, =SEARCH(“W”;A1) will return as 3. You can also use another optional argument to shorten search or omit some characters by specifying a starting point. =SEARCH(“W”;P35;4) will start searching from 4th character therefore return as 6 (by automatically omitting first W).
MID function returns a part of text with given starting point and length from within a text string. =MID(A1;3;4) will return as “W13W”.
Now SEARCH and MID functions are explained, we can move to our text selection formula.
We are going to use SEARCH function to determine a starting point for the MID function. Example function above re-written in this manner:
By using SEARCH function, we can select text in Excel with a known character without the need of knowing exact position of that character inside the target text. Here are more examples for further clarification:
Cell Text Formula Result
A1 4552-09-Y34562-345-R35 =MID(A1;SEARCH(“Y”;A1);6) Y34562
A2 42-1414-444-Y22609-4Q2 =MID(A2;SEARCH(“Y”;A2);6) Y22609
A3 009-Y82097-T0834-26133 =MID(A3;SEARCH(“Y”;A3);6) Y82097
A4 482199-2840Z-42-Y98274 =MID(A4;SEARCH(“Y”;A4);6) Y98274
As you can see from examples, you can easily extract text from string by using this formula.