I have a row with phone numbers and I want to highlight duplicates using conditional formatting. Problem is the numbers are often entered sloppily, sometimes there are whitespaces and sometimes a number starts with 0.
For example, the data could look like this:
123 456 78
0 123 45678
12345678
54312344
0543 12344
(the first 3 numbers and the last 2 should be highlighted as duplicates)
The formula I have written so far is this:
=COUNTIF(D:D,IF(LEFT(D1) = "0",SUBSTITUTE(RIGHT(D1,LEN(D1) - 1)," ",""), SUBSTITUTE(D1, " ", ""))) > 1
But that one does not work and I cannot figure out why.
