FilterXML #Value Error

[rkaczano]

I am getting a #VALUE error on a FilterXML function.

I have a long string of appended text sub strings that have been brought together using the TextJoin () function.

I am then spilling a unique list from that long appended string as follows:

=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(F2,",","</y><y>")&"</y></x>","//y"))

I know this works as I am already doing it on another text string using the same formulas.

The only thing that is different in the second example that is not working is that some of those substrings have "(", "*", and " ") in them. I know that the FilterXML function will produce a #VALUE error if the namespace has an invalid prefix. I am not sure what that means - but could the use of special characters in my substrings be generating the error?

Thank-you

 

[RoryA]

If any of the resulting items are blank (eg your data ends in a comma) you'd get an error.

 

[rkaczano]

There was an "&" that was causing me issues. Works now.

Thanks