Formula not recognizing ' in text string

[Michele12]

Good morning.

This post is related to a previous query I asked on this forum. In that query, I needed to take a comma separated list from one column and sort the values from the list into the appropriate columns. I was informed it would be easy to do in Excel 365, so that is what I used. I was given a formula to make the column headers from a list of the possible list values (=IFERROR(@INDEX(test,COLUMNS($D$1:D1)),"")) and then a formula to parse the data (=IF(D$1="","",IFERROR(TEXTJOIN(",",,SUBSTITUTE(FILTERXML("<A><B>"&SUBSTITUTE($B2,",","</B><B>")&"</B></A>","//B[contains(., '"&D$1&"="&"')]"),D$1&"=","")),"")) ). This worked great for most of the data until I realized it did not work for names/text strings that contained a apostrophe or prime symbol ('). The data was extracted from a public database so the data names are not easily modified.

In the attached file and gave a small easy to understand data set with similar values. You can see that the proper values are not returned for cells E2, G5, and J2; but it works well for the other values.

How can I modify the formula to recognize and parse strings with this character?

Thanks in advance.
Michele

[Glenn Kennedy]

Only one thread per topic is allowed. Thread closed.