Formula Help

[T-Meyers]

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 · Accepted solution]

@T-Meyers 

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

[#3]

@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. (?)