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.

Extract Text From String

Here are the 2 functions we are going to use to extract text from string with a known character:

  1. We are going to use SEARCH Function to determine the position of the known character.
  2. 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:

=MID(A1;SEARCH(“W”;A1);4)

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.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.