Monday, December 2, 2013

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 17th colon instead of the 3rd, 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 2nd and 3rd colons.

 

Cell: A2

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

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

This formula has changed the 3RD occurrence of ':' to ']', and the 2nd 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)

 

 

1 comment:

  1. excellent piece of information, I had come to know about your website from my friend kishore, pune,i have read atleast 8 posts of yours by now, and let me tell you, your site gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanx a lot once again, Regards, obiee training in hyderabad

    ReplyDelete