Excel - Extracting a Delimited String from a Text Value

Blog Index

 

I had a need to do something in Excel today that I had not done before.

 

I have this string:

 

All Products:Home:Appliances:Refrigerators:AJ275644. 

 

There are five "nodes" in that string, separated by colons.  I need to extract the third node (Appliances).

 

Using only the MID function is possible, of course, but that would require finding each successive occurrence of the colon.  If I needed the 17^th colon instead of the 3^rd, using MID by itself would result in a very cumbersome formula.

 

So let's put that string in cell A1 and see if we can extract all of the characters between the 2^nd and 3^rd colons.

 

Cell: A2

Formula: =SUBSTITUTE(SUBSTITUTE(A1,":","]",3),":","[",2)

Result: All:Products:Home[Appliances]Refrigerators:AJ275644

This formula has changed the 3^RD occurrence of ':' to ']', and the 2^nd occurrence of ':' to '['.

 

There is nothing special about using '[' and ']'.  I picked characters that I was pretty sure wouldn't be in the original string.  You can use any ASCII character, such as char(7) which is the Bell ding, or char(177) which is the plus/minus character.  For example, we could use SUBSTITUTE(A1,":",CHAR(177),2) to replace the second colon with the plus/minus sign.

 

Cell: A3

Formula: =MID(A2,FIND("[",A2)+1,FIND("]",A2)-FIND("[",A2)-1)

Result: Appliances

This extracts all characters between "[" and "]".

 

If desired, we can combine it together into one cell, replacing all references to cell A2 with the formula from cell A2:

 

Formula: =MID(SUBSTITUTE(SUBSTITUTE(A1,":","]",3),":","[",2),FIND("[",SUBSTITUTE(SUBSTITUTE(A1,":","]",3),":","[",2))+1,FIND("]",SUBSTITUTE(SUBSTITUTE(A1,":","]",3),":","[",2))-FIND("[",SUBSTITUTE(SUBSTITUTE(A1,":","]",3),":","[",2))-1)