 SOLVED

Highlighted

# Formula Help

I just need help creating a couple formulas based on the sheet attached. The amount of characters in between each "Hyphen" can be different at any time so I need formulas that pull any amount of characters between each set of Hyphens. (See notes on sheet attached) If anyone can help and send sheet back, I would really appreciate it.

Thank you

2 Replies
Highlighted
Solution

# Re: Formula Help

A couple of options:

Excel 2013 and later:

=INDEX(FILTERXML("<a><b>"&SUBSTITUTE(\$N2,"-","</b><b>")&"</b></a>","//b"),COLUMNS(\$A:A))

All versions:

=TRIM(MID(SUBSTITUTE(\$N2,"-",REPT(" ",LEN(\$N2))),LEN(\$N2)*(COLUMNS(\$A:A)-1)+1,LEN(\$N2)))

Both copied to the right as required.

Regards

Highlighted

# Re: Formula Help

@T-Meyers , or , building the formula one by one for each segment as in attachment

 =LEFT(\$N3,FIND("-",\$N3)-1) =MID(\$N3,LEN(A3)+2,FIND("-",RIGHT(\$N3,LEN(\$N3)-LEN(A3)-1),1)-1) =MID(\$N3,LEN(A3)+LEN(B3)+3,FIND("-",RIGHT(\$N3,LEN(\$N3)-LEN(A3)-LEN(B3)-2),1)-1) =MID(\$N3,LEN(A3)+LEN(B3)+LEN(C3)+4,FIND("-",RIGHT(\$N3,LEN(\$N3)-LEN(A3)-LEN(B3)-LEN(C3)-3),1)-1) =MID(\$N3,LEN(A3)+LEN(B3)+LEN(C3)+LEN(D3)+5,LEN(\$N3)-LEN(A3)-LEN(B3)-LEN(C3)-LEN(D3))

The beauty of formula shared by @Jos_Woolley is that it is a single and more consitent formula which can just be dragged right after entering in 1st cell. Though i am concerned if it would still work in case for any reason one happens to insert a column between the results , or change the order of

 Area Aisle Section Level Column

by cut column and insert columns etc. (?)