Find duplicates formula is ignoring punctuation marks

Question

In Google Sheets, I'm finding duplicates using the common approach of:

=IF(COUNTIF(A:A,A2)>1,"Duplicate","Unique")

But it is ignoring punctuation marks like '?'

For example, if I have 'wordA' and 'wordA?' it shows them as duplicates when they are not.

Is there any way around this?

Answer 1

The countif() function treats ? as a wildcard that matches any single character. wordA? will match wordA that has a trailing space. To get exact matches only, use filter(), like this:

=if( counta(iferror(filter(A:A, A:A = A2))) > 1, "Duplicate", "Unique" )

See countif().