How do I 'escape' a cell value to stop it being treated as a regular expression?

Using countif like this: COUNTIF($A$2:$A5,A6) to count the number of times the item has already appeared in a list.

It works for some values and fails for others because some of the cells have text in them that includes brackets and A3’s contents are treated as a regular expression.

I do not want to change the default behaviour so how can I annotate the formula to prevent A3’s content being treated (rather bizarrely) as a regular expression?

To enable better understanding could you please give examples of cell content of cells in which your formula works and in which not?

There exists no local setting, but only the global ones in Tools. What do you want to do? Why do you need to count the occurrences? There exists likely a total different way without COUNTIF for your goal. Other questions: Do you want to compare exact, whole cell content? Do you compare only texts, or numbers as well? Can you provide a tiny (!) example document?

I think a formula like: =SUMPRODUCT(FIND(A6,$A$2:$A$5)) should work, because FIND() does not use regular expressions.
Also you can use SUBSTITUTE() function to replace in the second parameter of COUNTIF() the brackets for \brackets ([ -> \]), but maybe are needed several SUBSTITUTE nested, one for every open and close bracket.

Since I often use similar expressions when sorting selections from data ranges by formulae, I was interested in the question though I didn’t actually encounter the problem as it was presented here. “Be prepared!” was the motto.

I attach a file dealing with the matter. It demonstrates a workaround generally useable with MATCH(), COUNTIF() and its relatives and it contains some experimental material concerning SEARCH(). You’re invited to have a look if interested.

Sorry! As a newby I am seemingly not allowed to attach files.
I uploaded the file therefore to my own webspace.
Edited: David fixed the attachment issue. Thanks!]

Attached: RegexOffLocal002.ods

Impressive file - thanks! :slight_smile: I uploaded/attached to your post, so you don’t need to use your own space. Feel free to roll-back/edit, though.