Extract text based on delimiters using formula. And then incorporate to INDEX MATCH

Hi All,

I was recently shown a way to extract specific text between delimiters using only a formula, but I can't for the life of me remember it.

For example, if I have the following text:

ONE/TWO/THREE/FOUR

and I would like to extract the third field 'THREE' then I can use a formula in another cell to do this. Does anyone know what this formula is?

Moreover, does anyone know how I could fit this formula into an INDEX MATCH function? That is to say, my current index match is picking up the entire string 'ONE/TWO/THREE/FOUR' but I want it to pick up only the third field

This will extract THREE given the format is always as your post

=TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",250)),501,250))

Hi John,

Thanks for this. It works perfect. But in the spirit of learning, could you explain what this formula does. I'd like to know what to change if I ever want to pick up the first, second or fourth field etc.

This is common method of separating text which has a common delimter.

Do the following:

in A1

ONE/TWO/THREE/FOUR

in B1

=TRIM(MID(SUBSTITUTE($A1,"/",REPT(" ",250)),(COLUMNS($A:A)-1)*250+1,250))

Drag the formula across to column E

You will see each element of the text string appear in B1 to E1

We substitute the "/" with 250 blanks (SUBSTITUTE($A1,"/",REPT(" ",250) so create a very large text string

As the cell is dragged across the cells we use MID to select 1 to 250 in B1, 251 to 500 in C1, 501 to 750 in D1 and 751 to 1000 in E1 using

(COLUMNS($A:A)-1)*250+1,250)

COLUMNS($A:A) increments: 1, 2 COLUMNS($A:B), 3, 4 as its dragged across the columns

So you will see THREE is in D1 which starts at MID 501 ... hence

=TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",250)),501,250))

Hope this explains how this works.