Forum Discussion

Copper Contributor

Oct 30, 2020

Formula for for writing common numbers

What formula will help me write common numbers from 2 cells? In cell A1 I have: 1,2,3. In cell B1 I have: 2,3,4. I would like cell C1 to write to me: 2 and 3.

excel

Formulas and Functions

SergeiBaklan

Diamond Contributor

Oct 30, 2020

Grosso1280

With formula that could be

=TEXTJOIN(",",1,
  XLOOKUP(
     FILTERXML("<r><n>" & SUBSTITUTE(A1,",","</n><n>") & "</n></r>",  "//n"),
     FILTERXML("<r><n>" & SUBSTITUTE(B1,",","</n><n>") & "</n></r>",  "//n"),
     FILTERXML("<r><n>" & SUBSTITUTE(B1,",","</n><n>") & "</n></r>",  "//n"),
""))