I am trying to use =TEXTJOIN(";",TRUE,UNIQUE(TEXTSPLIT(A1,";"))) to remove duplicate values from a semicolon separated list of names (as a string in a single cell on each of many rows). The formula does not return an error but the output includes all of the duplicates from the original input which is unexpected since this formula is supposed to remove duplicate values from the input array.
In case it was caused by issues with the data or with my Excel instance, I updated Excel to Version 2504 Build 16.0.18730.20122 (Windows 64 bit) and opened a new workbook with "Identical" in A1 and B1. I then put =Unique(A1:B1) in cell C1 and the output is "Identical" in cell C1 and D1, ie, the issue persists. You can see my test in the below screenshot. I also tested on Excel365 for Mac and got the same result.
Is this a bug or am I misusing the formula somehow?
TEXSPLIT is creating a horizontal array of values - if each value was in a different cell, they'd be in columns. The UNIQUE function has three arguments - Array, [ByCol], [Exactly Once]. If ByCol is FALSE or omitted, the function will assume it needs to look at rows to find a matching value. So, since TEXTSPLIT is creating columns, the function has nothing to look at.
To fix it, just include TRUE for the [ByCol] argument, like this: UNIQUE(TEXTSPLIT(A1,";"),TRUE,)
And the final formula would be =TEXTJOIN(";",TRUE,UNIQUE(TEXTSPLIT(A4,";"),TRUE,))
