Aug 18 2021 02:54 PM
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
Aug 18 2021 02:59 PM
=INDEX(FILTERXML("<y><z>"&SUBSTITUTE(A6,"/","</z><z>")&"</z></y>","//z"),B6+1)
Note: Does not work in Excel Online.
Aug 18 2021 03:00 PM
SolutionAug 19 2021 07:09 AM
In general you don't need INDEX()
=FILTERXML("<y><z>"&SUBSTITUTE(A6,"/","</z><z>")&"</z></y>","//z[position()=" & B6+1 & "]")
Aug 19 2021 08:05 AM
Aug 19 2021 08:19 AM
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.
Aug 19 2021 08:44 AM
Aug 18 2021 03:00 PM
SolutionUse
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A6,"/",REPT(" ",255),8),"/",REPT(" ",255),8),255,255))