Partial search and conditional format

[srizki]

I have in column A some account numbers, I want to find the duplicate, I used conditional formula to do this. But the problem is that many account numbers are duplicate but not same, for example number could be </SPAN>2168616100 and also 216861610, or </SPAN>91829776 and 918297762 these are duplicate but since they are not the same size the formula does not pick it up. </SPAN>
I need a formula that should see LEFT five digit or letters and find duplicate of it.</SPAN>

Thanks</SPAN>

 

[Mohammad Basem]

In conditional formatting, the formula=SUM(IF(LEFT(A1,5)=LEFT($A$1:$A$16,5),1,0)) > 1 highlights all duplicates by comparing the first 5 digits. Change the range $A$1:$A$16 as per your data.

 

[srizki]

Thanks you very much.

 

[Mohammad Basem]

Al-hamdullah, you are most welcome and thank you for the feedback!