How to get the unique value from a cell which repeats multiple times in excel?

How to get the unique value from a cell which repeats multiple times in excel?

Hi All i have a data of around 50000 candidates and one of the column contains the subject like below .

I want a formula in Column B to get the Unique Value from column A.
If all the values in "," are same then i need the Single value else all the cell Data.(There may be multiple comma separated entries)
I tried find formula but is not working.
Thanks in Advance.

Solutions/Answers:

Answer 1:

This will work for any number of comma-separated entries:

=IF(REPT(LEFT(A2,FIND(",",A2&",")),1+LEN(A2)-LEN(SUBSTITUTE(A2,",","")))=A2&",",LEFT(A2,FIND(",",A2)-1),A2)

Regards

Answer 2:

Bit of a mess but this will work:

=IF(ISNUMBER(SEARCH(MID($A2,1,SEARCH(",",$A2,1)-1),$A2,LEN(MID($A2,1,SEARCH(",",$A2,1)-1)))),IF(ISNUMBER(SEARCH(MID($A2,1,SEARCH(",",$A2,1)-1),$A2,LEN(MID($A2,1,SEARCH(",",$A2,1)-1))*2)),MID($A2,1,SEARCH(",",$A2,1)-1),$A2),$A2)

If search finds the first word again past the length of it it checks again past double it’s length. If that’s a match it returns just that word and otherwise returns the entire cell.

I will keep trying to find a more elegant solution but for now, this’ll do. Well providing you are always searching on an index of 3. If you aren’t I can still do it but would like to use helper columns (or have a crack at VBA which is easily up to the task, I wan’t to see effort first though as I won’t code for free as a rule of thumb)

References

Loading...