Formula for extracting text

Occasional Contributor

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



Note: Does not work in Excel Online.


best response confirmed by ezflow (Occasional Contributor)




=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.