SOLVED

New Contributor

# Formula for extracting text

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

# Re: Formula for extracting text

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

# Re: Formula for extracting text

Use

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

# Re: Formula for extracting text

Thank you, this worked!!!

# Re: Formula for extracting text

In general you don't need INDEX()

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

# Re: Formula for extracting text

Praise the one who knows XML.

# Re: Formula for extracting text

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.

# Re: Formula for extracting text

Very comprehensive list of examples.

Copied it into my big book of Excel knowledge.