SOLVED

Formula for extracting text

%3CLINGO-SUB%20id%3D%22lingo-sub-2662835%22%20slang%3D%22en-US%22%3EFormula%20for%20extracting%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2662835%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3CBR%20%2F%3EI'm%20trying%20to%20write%20a%20formula%20that%20would%20extract%20text%2C%20based%20a%20condition.%3CBR%20%2F%3ESo%20for%20example%20if%20cell%20B6%20%3D%208%3CBR%20%2F%3EGet%20the%20text%20between%20the%208th%20and%209th%20Forward%20switches%2C%20which%20is%20%222021%20JE%22%20from%20cell%20A6%3CBR%20%2F%3E%3CBR%20%2F%3EValue%20of%20cell%20A6%3C%2FP%3E%3CP%3E%2F%2Fnchprv%2Fprod%2Fdata%2FDept-HR%2FHR-MBU%2FJob%20Evaluation%2F2021%20JE%2F2021_Finance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2662835%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-2662883%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20extracting%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2662883%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1124563%22%20target%3D%22_blank%22%3E%40ezflow%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(FILTERXML(%22%3CY%3E%3CZ%3E%22%26amp%3BSUBSTITUTE(A6%2C%22%2F%22%2C%22%3C%2FZ%3E%3CZ%3E%22)%26amp%3B%22%3C%2FZ%3E%3C%2FY%3E%22%2C%22%2F%2Fz%22)%2CB6%2B1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ENote%3A%20Does%20not%20work%20in%20Excel%20Online.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2662886%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20extracting%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2662886%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1124563%22%20target%3D%22_blank%22%3E%40ezflow%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DTRIM(MID(SUBSTITUTE(SUBSTITUTE(A6%2C%22%2F%22%2CREPT(%22%20%22%2C255)%2C8)%2C%22%2F%22%2CREPT(%22%20%22%2C255)%2C8)%2C255%2C255))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2665352%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20extracting%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2665352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20you%20don't%20need%20INDEX()%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DFILTERXML(%22%3CY%3E%3CZ%3E%22%26amp%3BSUBSTITUTE(A6%2C%22%2F%22%2C%22%3C%2FZ%3E%3CZ%3E%22)%26amp%3B%22%3C%2FZ%3E%3C%2FY%3E%22%2C%22%2F%2Fz%5Bposition()%3D%22%20%26amp%3B%20B6%2B1%20%26amp%3B%20%22%5D%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,
I'm trying to write a formula that would extract text, based a condition.
So for example if cell B6 = 8
Get the text between the 8th and 9th Forward switches, which is "2021 JE" from cell A6

Value of cell A6

//nchprv/prod/data/Dept-HR/HR-MBU/Job Evaluation/2021 JE/2021_Finance

7 Replies

@ezflow 

=INDEX(FILTERXML("<y><z>"&SUBSTITUTE(A6,"/","</z><z>")&"</z></y>","//z"),B6+1)

Note: Does not work in Excel Online.

 

best response confirmed by ezflow (New Contributor)
Solution

@ezflow 

Use

 

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A6,"/",REPT(" ",255),8),"/",REPT(" ",255),8),255,255))

Thank you, this worked!!!

@Detlef Lewin 

In general you don't need INDEX()

=FILTERXML("<y><z>"&SUBSTITUTE(A6,"/","</z><z>")&"</z></y>","//z[position()=" & B6+1 & "]")

@Sergei Baklan 

Praise the one who knows XML.

 

@Detlef Lewin 

I only know google a bit. Here arrays - Excel - Extract substring(s) from string using FILTERXML - Stack Overflow somewhere in the middle quite useful summary with XML elements. 

@Sergei Baklan 

Very comprehensive list of examples.

Copied it into my big book of Excel knowledge.