CODEWITHSUNDEEP
excel-formulaexcel-2007
MTem
MTem

Reputation: 1

Extract various lengths of text before quotes within text string, with multiple instances within the string sometimes required (Excel)

I have a large amount of data with various measurements at the end of the descriptions that I want to put at the front. Text to columns won't work due to varying lengths, and I haven't been able to figure out how to get a combination of mid/len/left/right/search to yield the results I need.

My data is like this (each line within one cell):

TEE BRASS 2"
TEE BRASS 2" X 1"
TEE BRASS 3" X 2"
TEE BRASS DWV 2"
TEE COPPER 1"
TEE COPPER 1" X 1-1/2"
TEE COPPER 1-1/2" X 3/4"

My end goal is to have all the measurements in the front (still each line within one cell):

2" TEE BRASS
2" X 1" TEE BRASS
3" X 2" TEE BRASS
2" TEE BRASS DWV
1" TEE COPPER
1" X 1-1/2" TEE COPPER
1-1/2" X 3/4" TEE COPPER

Once separated I can concatenate/string it all together no problem. I basically just need a way to take everything in front of " and put it into a separate column, and if there are 2 or 3 instances of ", I need those each either in their own separate columns, or within the same column as any other measurements.

I'm running Excel 2007 (going to be asking the boss for an updated version very soon...).

Thanks!

Upvotes: 0

Views: 27

Answers (1)

Scott Craner
Scott Craner

Reputation: 152555

This formula will parse the data as you want:

=MID(A1,FIND("""",A1)-AGGREGATE(15,6,FIND("""",A1)-FIND("}}}",SUBSTITUTE(A1," ","}}}",ROW(INDIRECT("1:" & LEN(A1) - LEN(SUBSTITUTE(A1," ",""))))))/(FIND("""",A1)-FIND("}}}",SUBSTITUTE(A1," ","}}}",ROW(INDIRECT("1:" & LEN(A1) - LEN(SUBSTITUTE(A1," ",""))))))>0),1)+1,999) & " " & LEFT(A1,FIND("""",A1)-AGGREGATE(15,6,FIND("""",A1)-FIND("}}}",SUBSTITUTE(A1," ","}}}",ROW(INDIRECT("1:" & LEN(A1) - LEN(SUBSTITUTE(A1," ",""))))))/(FIND("""",A1)-FIND("}}}",SUBSTITUTE(A1," ","}}}",ROW(INDIRECT("1:" & LEN(A1) - LEN(SUBSTITUTE(A1," ",""))))))>0),1))

It will find the first "word" that has " in it, using the,

AGGREGATE(15,6,FIND("""",A1)-FIND("}}}",SUBSTITUTE(A1," ","}}}",ROW(INDIRECT("1:" & LEN(A1) - LEN(SUBSTITUTE(A1," ",""))))))/(FIND("""",A1)-FIND("}}}",SUBSTITUTE(A1," ","}}}",ROW(INDIRECT("1:" & LEN(A1) - LEN(SUBSTITUTE(A1," ",""))))))>0),1)

It then splits the string at the space before that "word".

It then concatenates using & the two returns.

NOTES:

This assumes that the "measurements" all have " in them.

It also assumes the split should be done at the first space before the " is found. So 1 - 1/4" will split at the space between 1 - and 1/4"

enter image description here

For 2007 you will need to use this array formula:

=MID(A1,FIND("""",A1)-SMALL(IF(FIND("""",A1)-FIND("}}}",SUBSTITUTE(A1," ","}}}",ROW(INDIRECT("1:" & LEN(A1) - LEN(SUBSTITUTE(A1," ",""))))))>0,FIND("""",A1)-FIND("}}}",SUBSTITUTE(A1," ","}}}",ROW(INDIRECT("1:" & LEN(A1) - LEN(SUBSTITUTE(A1," ",""))))))),1)+1,999) & " " & LEFT(A1,FIND("""",A1)-SMALL(IF(FIND("""",A1)-FIND("}}}",SUBSTITUTE(A1," ","}}}",ROW(INDIRECT("1:" & LEN(A1) - LEN(SUBSTITUTE(A1," ",""))))))>0,FIND("""",A1)-FIND("}}}",SUBSTITUTE(A1," ","}}}",ROW(INDIRECT("1:" & LEN(A1) - LEN(SUBSTITUTE(A1," ",""))))))),1))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

Upvotes: 1

Related Questions