I am trying to figure out how to remove duplicate values from with an Excel cell. My columns have values separated by a semi-colon. For example, in Column A I have these 3 rows with the values below.
3;5;6
2;2;4
9;5;12;12
What I am wanting to do in an adjacent column, call it column B, have a formula that returns:
3;5;6
2;4
9;5;12
What formula or function would I need to use to achieve this?
The following formula can work with some limited conditions:
-using >= excel2019 version.
-all numbers in the strings > 0.
-all numbers in the string are integer.
-the maximum number in the string = 10^6
You can change $1:$10 or $1:$100,..
C2=TEXTJOIN(";",,IF(1=FREQUENCY(IFERROR(MATCH(ROW($1:$10),--TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",LEN(B2))),(ROW(INDIRECT("A1:A" & 1+ LEN(B2)-LEN(SUBSTITUTE(B2,",",""))))-1)*LEN(B2)+1,LEN(B2))),0),1000),IFERROR(MATCH(ROW($1:$10),--TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",LEN(B2))),(ROW(INDIRECT("A1:A" & 1+ LEN(B2)-LEN(SUBSTITUTE(B2,",",""))))-1)*LEN(B2)+1,LEN(B2))),0),1000)),ROW($1:$10),""))
If you have office 365 then you can try below formula and see if it helps you with your requirement.
=TEXTJOIN(";",TRUE,(UNIQUE(FILTERXML("<t><d>"&SUBSTITUTE(A2,";","</d><d>")&"</d></t>","//d"))))
For FILTERXML part, I will recommend going through below link on stackoverflow which creates array of individual elements from base string:
Excel - Extract substring(s) from string using FILTERXML
Rest of the structure is pretty standard which uses UNIQUE to get list of unique items and then we join them back together using TEXTJOIN.
Another Office 365 or Excel 2019 formula solution
In B1, array (CSE) formula copied down :
=TEXTJOIN(";",1,IF(ISNUMBER(FIND(";"&ROW(A$1:A$9999)&";",";"&A1&";")),ROW(A$1:A$9999),""))
