SOLVED

Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1424765%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424765%22%20slang%3D%22en-US%22%3E%3CP%3EI%20just%20need%20help%20creating%20a%20couple%20formulas%20based%20on%20the%20sheet%20attached.%20The%20amount%20of%20characters%20in%20between%20each%20%22Hyphen%22%20can%20be%20different%20at%20any%20time%20so%20I%20need%20formulas%20that%20pull%20any%20amount%20of%20characters%20between%20each%20set%20of%20Hyphens.%20(See%20notes%20on%20sheet%20attached)%20If%20anyone%20can%20help%20and%20send%20sheet%20back%2C%20I%20would%20really%20appreciate%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1424765%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424861%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F572655%22%20target%3D%22_blank%22%3E%40T-Meyers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20couple%20of%20options%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%202013%20and%20later%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23000080%22%3E%3DINDEX(FILTERXML(%22%3CA%3E%3CB%3E%22%26amp%3BSUBSTITUTE(%24N2%2C%22-%22%2C%22%3C%2FB%3E%3CB%3E%22)%26amp%3B%22%3C%2FB%3E%3C%2FA%3E%22%2C%22%2F%2Fb%22)%2CCOLUMNS(%24A%3AA))%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20versions%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DTRIM(MID(SUBSTITUTE(%24N2%2C%22-%22%2CREPT(%22%20%22%2CLEN(%24N2)))%2CLEN(%24N2)*(COLUMNS(%24A%3AA)-1)%2B1%2CLEN(%24N2)))%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBoth%20copied%20to%20the%20right%20as%20required.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1424923%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1424923%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F572655%22%20target%3D%22_blank%22%3E%40T-Meyers%3C%2FA%3E%26nbsp%3B%2C%20or%20%2C%20building%20the%20formula%20one%20by%20one%20for%20each%20segment%20as%20in%20attachment%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22647%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22132%22%3E%3DLEFT(%24N3%2CFIND(%22-%22%2C%24N3)-1)%3C%2FTD%3E%3CTD%20width%3D%22132%22%3E%3DMID(%24N3%2CLEN(A3)%2B2%2CFIND(%22-%22%2CRIGHT(%24N3%2CLEN(%24N3)-LEN(A3)-1)%2C1)-1)%3C%2FTD%3E%3CTD%20width%3D%22132%22%3E%3DMID(%24N3%2CLEN(A3)%2BLEN(B3)%2B3%2CFIND(%22-%22%2CRIGHT(%24N3%2CLEN(%24N3)-LEN(A3)-LEN(B3)-2)%2C1)-1)%3C%2FTD%3E%3CTD%20width%3D%22132%22%3E%3DMID(%24N3%2CLEN(A3)%2BLEN(B3)%2BLEN(C3)%2B4%2CFIND(%22-%22%2CRIGHT(%24N3%2CLEN(%24N3)-LEN(A3)-LEN(B3)-LEN(C3)-3)%2C1)-1)%3C%2FTD%3E%3CTD%20width%3D%22119%22%3E%3DMID(%24N3%2CLEN(A3)%2BLEN(B3)%2BLEN(C3)%2BLEN(D3)%2B5%2CLEN(%24N3)-LEN(A3)-LEN(B3)-LEN(C3)-LEN(D3))%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20beauty%20of%20formula%20shared%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%3E%40Jos_Woolley%3C%2FA%3E%26nbsp%3Bis%20that%20it%20is%20a%20single%20and%20more%20consitent%20formula%20which%20can%20just%20be%20dragged%20right%20after%20entering%20in%201st%20cell.%20Though%20i%20am%20concerned%20if%20it%20would%20still%20work%20in%20case%20for%20any%20reason%20one%20happens%20to%20insert%20a%20column%20between%20the%20results%20%2C%20or%20change%20the%20order%20of%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22647%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22132%22%3EArea%3C%2FTD%3E%3CTD%20width%3D%22132%22%3EAisle%3C%2FTD%3E%3CTD%20width%3D%22132%22%3ESection%3C%2FTD%3E%3CTD%20width%3D%22132%22%3ELevel%3C%2FTD%3E%3CTD%20width%3D%22119%22%3EColumn%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eby%20cut%20column%20and%20insert%20columns%20etc.%20(%3F)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

@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

 

Highlighted

@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 

AreaAisleSectionLevelColumn

 

by cut column and insert columns etc. (?)