Formula for extracting text

[ezflow]

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

[#2]

@ezflow 

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

Note: Does not work in Excel Online.

[#3 · Accepted solution]

@ezflow 

Use

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

[#4]

Thank you, this worked!!!

[#5]

@Detlef Lewin 

In general you don't need INDEX()

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

[#6]

@Sergei Baklan 

Praise the one who knows XML. :)

[#7]

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

[#8]

@Sergei Baklan 

Very comprehensive list of examples.

Copied it into my big book of Excel knowledge.