Search code examples
excelexcel-formulaconcatenationtextjoinmultiple-matches

Search row for matching partial strings then concatenate strings in new column

Apologies if this is something really obvious but egads it's driving me crazy and I can't find a non-array/helper box answer.

Excel table

I want to search cells D2 and E2 for matching numbers, and then concatenate these (using TEXTJOIN?) for each row in cell F2 (and then copy the formula down the column). So for eg I can see that D2 and E2 share unit 2, and cells D4 and E4 both have units 1 and 2 - so I'd like F4 to show "1,2". I want it to search for any and all numbers that match across both columns and return this in F as a comma-delimited concatenated string. Any help gratefully received.

Solution

  • As per the comments you can use FILTERXML() to retrieve the correct data:

    =IFERROR(TEXTJOIN(",",,FILTERXML("<t><s>"&SUBSTITUTE(D2&","&E2,",","</s><s>")&"</s></t>","//s[following::*=.]")),"None")

    For tips & tricks on how to use FILTERXML(), please see this post here on SO.