conditional format to flag the duplicate using wildcard

Question

Let say i want to check column D to see if there's duplicates.

I normally use this formula (in conditional formatting) to color the cell if there's multiple same value ( exact same value) :

=countif($D$1:$D,$D1)>1

But now i want to check if there's the similarity in first 3 letters, For example i want this to be warned as duplicates error.

How
Howy
Hows

I tried to use wildcards , it works on cell but not working when i use in conditional formatting.

=countif($D$1:$D ; left($D1,3) & "*")>1

Does anyone have a solution ?

Answer 1

The conditional formatting custom formula rule you quote should work just fine. Check that the Apply to range setting in the formatting rule is correct — it should start from row 1.

To skip blank cells, use this:

=len($D1) * (countif($D$1:$D , left($D1,3) & "*") > 1)