Extract Text From String With Known Position

You may need to get a part of a text string from within another text string. If you know starting position of the text you are trying to extract but length wary each time, here is a formula to extract text from string with known position.

Here is the example data set I am going to use to demonstrate this formula:

Extract Text From String With Known Position 1

I am going to extract country names from these strings. As you surely will notice, this task is a little problematic to be done using a general text function. That is because, country names vary in length. Therefore we need a formula that can measure the length of the text we are going to extract.

So the solution is creating a formula that will combine 2 text functions to achieve this kind of flexibility. We are going to use MID() Function to extract text and LEN() Function to measure the length of the text we want to extract.

Only requirement for this formula to work is you need to know the starting position of each text string you want to extract.

Here is how our formula to extract text from string with known position is structured:

Extract Text From String With Known Position 2

When you apply this formula into column B, next to the text strings, it will extract country names for you.

Extract Text From String With Known Position 3

 

You may also like...

3 Responses

  1. Leonid says:

    I your example subtracting “unnecessary” characters is redundant.
    MID(A2,6,LEN(A2)) is good enough.

  2. dan david says:

    works with =MID(A2;6;100)

Leave a Reply

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