I know, multiple questions has been asked with respect to duplicate in excel. However, I didnt find anything similar, so asking the same.

I have an excel consist of one columns which contains large set of records in the format below, say

XYZ/123/456.abc.def.bla.ghi

ABC/985/klm_nop

FGH/999/mon.pqr.stu

I want to compare for duplicate but by ignoring the first letter (till '/'). If it matches the highlighted part with any other set in the column then return me as a duplicate/highlight otherwise unique.

any help on this ? thanks in advance.

CodePudding user response：

So your question is not how to find duplicates, but how to get rid of the first part of your records.

You will achieve this with this formula: =MID(A1,FIND("/",A1) 1,LEN(A1)) - assuming the first entry is in A1

Then you can use conditional formatting for duplicate values.