Questions tagged [regex]

regex stands for "regular expressions". They are used to define a string search pattern that could be used in some web applications like Google editors to do input validation, search and replace among other similar tasks.

A regular expression (abbreviated regex or regexp and sometimes called a rational expression) is a sequence of characters that define a search pattern, mainly for use in pattern matching with strings, or string matching, i.e. "find and replace"-like operations.

Source: https://en.wikipedia.org/wiki/Regular_expression

176 questions
158
votes
10 answers

Google Sheets formula for "if contains"

I'm trying to figure out how to identify IF a list of items in one cell containing a value or string. EXAMPLE Cell A1 contains sites, sheets, docs, slides. I want cell B1 to display a 1 'if' cell A1 contains the string sites. FORMULA =if(A1 ??????…
Mr. B
  • 2,967
  • 11
  • 38
  • 46
84
votes
7 answers

Is there a way to search using regular expressions?

It would be very useful if Google provided a regular expression search. Is there a way to do this? I am not talking about search operators like site:, filetype:, AND, OR or "Text". I would like to search with a regular expression like .+[]^). For…
GarouDan
  • 943
  • 1
  • 7
  • 6
43
votes
3 answers

FILTER(condition OR condition) syntax?

I am using FILTER() to display a value from Sheet 2 where the Name columns match. My problem is where I want: =FILTER(Sheet2!A:F, Sheet2!A:A="Combat Medic" OR Sheet2!A:A="Universal") as OR results in an error. What is the correct syntax? Must I use…
29
votes
2 answers

How to use text from capturing groups in Google Docs regex replace?

I'm trying to match certain text and then replace with the text plus some extra characters. Minimal example text: #10 Oranges. These are citrus fruits Desired output: #10 Oranges. These are citrus fruits Regex: (#\d{1,2}[^.]*\.)\s* Replace with:…
User
  • 613
  • 2
  • 7
  • 16
14
votes
6 answers

Online regex preview tool

Is there any good regex webapps out there? I'd like to be able to set which type of regex syntax is used, enter some text and a regex and get matches highlighted. Preferably with different colors so it's easy to detect what in the regex is causing…
googletorp
  • 715
  • 9
  • 18
12
votes
3 answers

Is there a way to automatically extract information from emails (e.g. regex prices on gmail)?

Every month I get an email from my mobile carrier which tells me how much money my contract has cost me the last month, and I'd like to be able to form that into an iOS notification with IFTTT. The problem is that I can't just create a new recipe…
yspreen
  • 241
  • 2
  • 7
10
votes
3 answers

Which formula extracts the domain name from an email address (bob@example.com -> example.com)?

Which Google Spreadsheets formula do I use to get the domain part from an email address? Example: bob@example.com → example.com
nic
  • 8,884
  • 28
  • 80
  • 135
10
votes
5 answers

Count the number of words in a string in Google Sheets

I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-built Google spreadsheet function I may use?
10
votes
3 answers

Is it possible to search MediaWiki sites using regular expressions?

I'd like to search for phrases using regular expressions on sites such as Wikipedia and Wikimedia Commons. Is it possible to do this? Example regex search query: (facebook|google) (corporate history|websites) It's already possible to do the same on…
Anderson Green
  • 2,678
  • 3
  • 29
  • 56
8
votes
1 answer

Using regex in Google Sheets data validation

I'm trying to use a regex in the Google Sheets data validation in order to ban the input of special characters, so basically my allowed set should be [a-zA-Z0-9,./()]. I'm using the custom formula input with the following…
GuiNetto
  • 83
  • 1
  • 1
  • 3
7
votes
2 answers

Google Spreadsheets Query( "where a matches '/[A-Z+]/' ") condition

According to Google Query documentation: matches - A (preg) regular expression match. haystack matches needle is true if the regular expression in needle matches haystack. Examples: where country matches '.*ia' matches India and Nigeria, but …
Gleb
  • 313
  • 2
  • 4
  • 9
6
votes
1 answer

Google spreadsheet query language, match using regex?

I have a query formula that looks into a table and pulls names for example: A B 1 john, andrew blah 2 drew, mike blah I want it to show me all of the rows that involve drew, the query formula is select where A…
jason
  • 1,054
  • 6
  • 24
  • 41
5
votes
1 answer

Case insensitive character string filter for multiple individual columns (dynamic input for each)

I'm looking for an efficient way to filter a data set by multiple individual, case insensitive character strings for multiple columns in a Google docs spreadsheet. I can already achieve this by amending a formula with one new segment per column I…
user3794154
  • 51
  • 1
  • 4
5
votes
2 answers

COUNTIFS with multiple OR

I have two sheets, one is a dump sheet of values like below: and the second sheet is using values from this to the total. I am trying to get the total count for the corresponding zone and test name but only if the clash status column says "New" or…
4
votes
2 answers

Count Occurence of a Regex Search Term

Using CTRL+F indicates the number of occurrences of a search term. However, when using regex (via Find and Replace) there's no indication of count. Does anyone know how to find the number of occurrences when using regex?
IMB
  • 407
  • 7
  • 13
1
2 3
11 12