We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2014-05-12 04:33:13 +0200

Whippy gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-23 21:30:04.945479


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

ROSt52 gravatar imageROSt52 ( 2014-05-12 07:33:54 +0200 )edit

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?

Regina gravatar imageRegina ( 2014-05-12 22:09:05 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2014-05-12 22:02:53 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more

answered 2014-05-26 20:59:03 +0200

Lupp gravatar image

updated 2014-05-27 19:30:34 +0200

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 <url deleted=""> therefore to my own webspace. Edited: David fixed the attachment issue. Thanks!]

Attached: RegexOffLocal002.ods

edit flag offensive delete link more


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

David gravatar imageDavid ( 2014-05-26 21:14:03 +0200 )edit

Question Tools

1 follower


Asked: 2014-05-12 04:33:13 +0200

Seen: 929 times

Last updated: May 27 '14