## 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)

Loading...