Returning substring-after using FILTERXML

user_4851590 0
Viewed 142

In a now deleted question, the OP asked to return 1.3 and 0.4 from 1^1.3%2^0.4.

I immediately thought FILTERXML.

I was able to do it with:

=MID(FILTERXML("<a><b>"&SUBSTITUTE(A1,"%","</b><b>")&"</b></a>","//b"),FIND("^",FILTERXML("<a><b>"&SUBSTITUTE(A1,"%","</b><b>")&"</b></a>","//b"))+1,99)

But that seemed too repetative.

I found substring-after in XPATH 1.0, but cannot get it to do what I want. I first tried:

=FILTERXML("<a><b>"&SUBSTITUTE(A1,"%","</b><b>")&"</b></a>","//b[substring-after(., '^')]")

But that returned both full strings and :

=FILTERXML("<a><b>"&SUBSTITUTE(A1,"%","</b><b>")&"</b></a>","substring-after(//b, '^')")

Just returned an error.

Is there a way to use only FILTERXML to return the desired numbers?

excel-formula excel
1 Answers

Unfortunately XPATH in Excel cannot seem to return reworked values, instead use FILTERXML for filtering and returning those nodes of interest. Reworking those nodes need to be done outside the function.

If you are intested in a single FILTERXML usage, I'd suggest a double SUBSTITUTE:

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,"^","^%"),"%","</b><b>")&"</b></a>","//b[contains(preceding::*[1],'^')]")

Or:

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,"^","^%"),"%","</b><b>")&"</b></a>","//b[.*0=0]")

Might you be interested in filtering through FILTERXML. Maybe this would interest you?

user_97581940
Related