How to extract unique code from a column having multiple codes in is cell

Try with
simultaneously press shift+ctrl+enter

Formula:

Please Login or Register  to view this content.

copy paste down till blank

Hi

first off, as a disclaimer: it's a very slow calculating formula.

In H2 to extract numbers from 241000 to 243000


=AGGREGATE(15,6,ROW($241000:$244000)/((COUNTIF(F$2:F$840;"*"&ROW($241000:$244000)&"*")>0)*(COUNTIF(H$1:H1,ROW($241000:$244000))=0)),ROWS($1:1))


Edit: maybe the following one it's a little faster than my first one

=AGGREGATE(15,6,ROW($241000:$244000)/ISNUMBER(MATCH("*"&ROW($241000:$244000)&"*",F$2:F$840,0)),ROWS($1:1))



Regards

Hi,

please refer to the attachment: in column I've copied

=AGGREGATE(15,6,ROW($241000:$244000)/ISNUMBER(MATCH("*"&ROW($241000:$244000)&"*",F$2:F$840,0)),ROWS($1:1))

For your convenience, I put the formula in a elementary IFERROR

Please let me know which are unexpected results

AGGREGATE is avaliable only from Excel 2010 onwards. Maybe this one is a problem.

If you're using previous version of Excel:

=IFERROR(SMALL(IF(ISNUMBER(MATCH("*"&ROW($241000:$244000)&"*",F$2:F$840,0)),ROW($241000:$244000)),ROWS($1:1)),"")

to be confirmed with control+shift+enter before to be copied down (please refer ti file 2007).


Regards

Please try at I2
=IFERROR(AGGREGATE(15,6,ROW($241000:$244000)/(FREQUENCY(--TEXT(MID(SUBSTITUTE($F$2:$F$840," ",REPT(" ",99)),COLUMN(A$1:L$1)*99-98,99),"0;;;\0"),ROW($241000:$244000))>0),ROWS(I$1:I2)),"")

or for Excel2007
J2 and press Ctrl+Shift+Enter
=IFERROR(SMALL(IF(FREQUENCY(--TEXT(MID(SUBSTITUTE($F$2:$F$840," ",REPT(" ",99)),COLUMN(A$1:L$1)*99-98,99),"0;;;\0"),ROW($241000:$244000)),ROW($241000:$244000)),ROWS(J$1:J2)),"")