Hi there,

I have data in a single column that looks sth like this:

CTE_DIV_96657
TOMMY_DIV_94434
PHILLIP CHUA - DIV 95343

As you can see, the text and numbers are seperated sometimes by spaces and sometimes by _ or -. I need to seperate this data into 3 seperate columns

i.e. CTE | DIV | 96657

Then, I need to highlight duplicates in the last column (96657,etc) without deleting the duplicates as would happen if I used Advanced Filter > Unique records only

I have tried using the formula =RIGHT(A1,5), but I have no way of checking for duplicates by hightlighting repeated entries (I need to conditional format to detect duplicate numbers )

Can anyone please help?