Duplicate conditional formatting and COUNTIF text cells containing numbers with trailing zeros-CodePudding

Using Google Sheets, I have a column with identifier data, which look like : "1.1", "1.1a", "1.2", ..., "1.10", "1.11a". I make sure to format these as plain text, as otherwise, the number value "1.1" would be interpreted as identical to "1.10", which is not my intenion. This works fine.

However, when I use COUNTIF, it seems these values are still interpreted as numbers.

My motivation is to highlight possible duplicates in this column. I use conditional formatting with the formula =COUNTIF(A:A, A1) > 1.

Is there another function I can use, or something I can fix with cell formatting, which will allow me to highlight duplicated properly?

CodePudding user response：

I found my own workaround which works for my scenario:

=COUNTIFS(A:A,A1,ARRAYFORMULA(LEN(A:A)),LEN(A1))>1

(I am answering my own question. I will leave the question open for a more elegant answer, and will accept my own if no such answer appears.)

CodePudding user response：

This should work. Not sure it's any more elegant.

=ARRAYFORMULA(COUNTIF(TO_TEXT(A:A),TO_TEXT(A1)))>1