[Solved] Comparing comma separated numbers in cells

by


In Excel 2016 (but NOT Excel 2013), you can use the following array-entered formula.

=TEXTJOIN(",",TRUE,IFERROR(1/(1/(ISNUMBER(FIND(","&TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),seq_99,99))&",",","&A2&","))))*TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),seq_99,99)),""))

seq_99 is a Named Formula

Refers to:  =IF(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))=1,1,(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))-1)*99)

To enter an array formula, after entering the formula in the cell, confirm by holding down ctrl + shift while hitting enter. If you do it correctly, Excel will place braces {...} around the formula.

enter image description here

4

solved Comparing comma separated numbers in cells