SOLVED

Formula for extracting text

Occasional 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 (Occasional 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.

 

www.000webhost.com