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.

Filter by
Sorted by
Tagged with
61
votes
9answers
98k views

Is there a way to search in Google using regular expressions (regex)?

It would be very useful if Google provided a regular expression search. Is there a way to do this? (OBS: I am not talking about false regular expressions like site:, filetype:, AND, OR or "Text". I ...
43
votes
7answers
190k views

Google Sheets formula for “if contains”

I'm trying to figure out how to identify IF a list of items in one cell contains a value or string. EXAMPLE Cell A1 contains sites, sheets, docs, slides. I want cell B1 to display a 1 'if' cell ...
32
votes
2answers
59k views

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 ...
14
votes
6answers
3k views

Online regex preview tool [closed]

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 ...
12
votes
2answers
8k views

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 ...
10
votes
3answers
6k views

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 ...
9
votes
5answers
37k views

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?
8
votes
3answers
8k views

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
8
votes
3answers
955 views

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|...
6
votes
1answer
4k views

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 ...
4
votes
1answer
6k views

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 ...
4
votes
4answers
105 views

Joining two rows of data with alternating delimiters

I'm working with two rows of values that are related to each other and trying to build a formula that will compile the data into one cell, with syntax that uses two delimiters. If a cell in the ...
4
votes
1answer
4k views

How can I search Gmail for a Windows activation key?

Somewhere lost in my Gmail is a Windows key. It's by itself in an email but I cannot find it. It's formatted like so: XXXXX-XXXXX-XXXXX-XXXXX-XXXXX If Gmail supported regular expressions it would be ...
4
votes
1answer
5k views

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 ...
3
votes
3answers
4k views

Finding and removing periods

I'm using a Google Sheets and trying to create a formula to remove a period and anything that comes before it in a cell. For example: idontwantthis.iwantthis I have used this in the past: =(RIGHT(...
3
votes
2answers
10k views

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 ...
3
votes
2answers
5k views

Is there a way to search and replace a regular expression in Google Docs spreadsheets?

The problem is that I entered a lot of formulas like =round(x+y) into a Google Docs spreadsheet. Now I need them exported to MS Excel and the latter only accepts =round(x+y,0) as a valid format. ...
3
votes
2answers
803 views

Search a text for specific text, then multiple if statements depending on what's there

I've got a weird question about Google Sheets. First, here's what I've got so far, but it returns an error "Function REGEXEXTRACT parameter 2 value "My Text here" is not a valid regular expression." ...
3
votes
1answer
2k views

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 ...
3
votes
1answer
949 views

How to sort by date with Google Spreadsheet?

Consider the following text in a single column in a spreadsheet: AAPL 2015 Dec 18 C @ 185.000 AAPL 2016 Mar 18 C @ 160.000 ... How to sort by date? I tried extracting the date using regex, but it ...
3
votes
1answer
498 views

Using regexreplace to surround numbers by parentheses

The data I'm starting with: 1 Hello 2 Just another 22 line 300 Yeah, the first step is to admit you need help I want the "finished" version to look like: (1) Hello (2) Just another (22) ...
3
votes
1answer
103 views

Which regular expression flavour is used by the MediaWiki search and replace dialog?

I recently discovered this search and replace dialog in MediaWiki and have been using it more and more: But I can't seem to find the documentation on it. On the MediaWiki website I always find info ...
3
votes
1answer
121 views

What tools exist that can do string matching (ex. regex) websearches? [closed]

Is there any web search tool that can do the following: Perform a web search where one of the keywords is a string pattern, such as ####suffix, and will match the best result that matches a string ...
2
votes
2answers
6k views

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 ...
2
votes
3answers
52 views

Find and extract words/names from a string beginning with specific character

I've got some rows in Google Sheets that contain strings. I want to extract certain parts from this string and enter them into another cell: specifically, I want to extract words/names that begin with ...
2
votes
2answers
1k views

Appending text to each column in Google Sheets

I have a Google Sheets that has about 200 columns. Each one is a name, and to each one, I want to append -banner.jpg. For example: abcde fghij klmno will all be updated to: abcde-banner.jpg fghij-...
2
votes
1answer
91 views

How to extract multiple paths from a single string of URLs in Google Sheets?

How can I get a subrange of second to last element in a formula under Google Sheets? I'm trying to extract all occurrences of https://example.com/* in a cell which may contain multiple URLs scattered ...
2
votes
1answer
139 views

Searching for a sentence that I only know part of it using Google's operators

I am looking for a particular combination of google hacking commands to find a sentence. For example, I am looking for ”where eagles dare” In this case, I don’t have part of a sentence like the ...
2
votes
2answers
127 views

Query or Filter formula to 'search for' and 'return' multiple words

I am a basic user of Google Sheets and need help creating a new formula. I am trying to filter a worksheet so that it returns particular information into another sheet. The filter I have created is ...
2
votes
2answers
960 views

Google Search with Regular Expressions [duplicate]

Is it possible to use regular expressions or very simple regular expressions in google search? Like search the term "goog?e", where the ? indicates a character or a number.
2
votes
1answer
46 views

Sum value in cells that contain both number and text

I need to sum a range of cells with text in them. Example: a1= "25,00 market" b1="24,00 credit card" c1="75,11 food" a2= "10,00 sweets" b2="9,00 market" c2="24,00 keyboard" I want to sum ...
2
votes
0answers
881 views

How to write a clean Gmail filter for SVN commits?

I have a dozen or so SVN projects that generate a few dozen email messages every day, each with subject line like this [SVN projectname] in branch/trunk list of files. I want to send these email ...
1
vote
3answers
465 views

How can I use the capturing group in a function?

=REGEXREPLACE("string", "(ng)", UPPER("$1")) The above returns "string", but should return "striNG". I suspect that this is because UPPER is capitalizing the string "$1" rather than the string with ...
1
vote
2answers
380 views

How do I use regexmatch inside sumproduct?

This works: =sumproduct($J$6:$J,($A$6:$A="Order")*($G$6:$G="T8")) This does not. =sumproduct($J$6:$J,($A$6:$A="Order")*(Regexmatch($G$6:$G,"T8")) produces formula parse error. This =regexmatch(...
1
vote
1answer
392 views

Matching a cell value to a category from patterns table

I'm trying to categorize a cell value in Google Spreadsheets, based on a categories table, so that depending on the value in the cell, the correct category will be determined. Given a categories ...
1
vote
2answers
22 views

Use Google Sheets to create column C by deleting any phrases that contain word found in column B in column A

I have a list in column A and I want to remove any cells that contain any word.
1
vote
1answer
35 views

Filter spreadsheet by user-entered value in first row

In Google Sheets, I want to set up a filtered view like this. Row 1 = column headings Row 2 = filter criteria Rows 3... = data I want the user to type a value into any column of row 2, and ...
1
vote
2answers
17 views

How to get a true when a character is NOT in range

I want to filter all cells in a range which are not empty and does not contain a certain character. So I tried everything with regexmatch =filter(Planning!G$5:G$37; regexmatch(Planning!G$5:G$37 ; "![...
1
vote
1answer
1k views

Google Sheet Query smarter way to get multiple criteria

I use a spreadsheet where I want a list of items which contains multiple criteria. (I need all cells in a range which contains the letter 'a - g' The formula I use now is =query(Planning!G5:G37; "...
1
vote
2answers
1k views

How to perform multiple operations where the IF statement is true?

The function below is what I use to handle contact information (email/phone numbers). If the entry is an email id (has text), it will keep it as is. When a phone number is entered, it does a variety ...
1
vote
1answer
2k views

Google Sheets: REGEXEXTRACT Formula to remove characters not inside [brackets] but leave , commas?

I'm trying to create a formula that uses either =REGEXEXTRACT or =REGEXREPLACE to remove all characters not inside [brackets] but leaving the ,,, commas where they are. For example... ➜ Text to ...
1
vote
2answers
362 views

Preventing certain words from appearing on a Google Sheets

Let's say that I have a big Google spreadsheet that multiple users have access to, and I'm not able to control which users have access to it. I have a concern for trolls. How would I prevent people ...
1
vote
1answer
1k views

Why doesn't Google give search results for '*' [asterisk]?

You can search for anything on Google, right? At least, so I thought. I was trying to see how the special characters get encoded in the url, like how & gets converted to %26. I started searching ...
1
vote
1answer
266 views

RegexReplace produces an error

I have two sheets of data as per below: ╔═══════════╦══════╦══════════════╗ ║ SheetName ║ Cell ║ CellValue ║ ╠═══════════╬══════╬══════════════╣ ║ Sheet1 ║ B1 ║ ='sheet2'!A2 ║ ║ Sheet2 ║ ...
1
vote
2answers
40 views

Find and place in a column, the first non-blank cell in each row from a range of columns

I'm attempting to create an ARRAYFORMULA that can extract the first non-blank cell value on each row in a range of columns. My latest attempt is this: =TRANSPOSE(QUERY(TRANSPOSE( A2:D6),"select * ...
1
vote
1answer
26 views

Google Sheets formula for “if contains” for grading multiple choice quiz

I want to use a Google Sheets formula to correct a multiple choice survey that I built using Squarespace. The results get dumped into Google sheets. For example, if the respondent selects choice: A....
1
vote
1answer
67 views

Syntax for Unicode characters in regexes in Google Sheets?

What's the syntax to use for general Unicode characters in regular expressions in Google Sheets? Use case: When using REGEXREPLACE with user submitted strings in Google Sheets, I have come across a ...
1
vote
1answer
23 views

How do I make the “Text contains” evaluate any word written in the cell?

I just want the cell in Google Sheets to change its fill color when any text is inputted in the cell? But it has to be text excluding numbers. Please help!
1
vote
1answer
37 views

Checking whether two columns contain the same string of numbers

Some time ago I have put together a perfectly working formula but now it's not working and I can't tell why (I may have changed something and don't remember that). Column B contains data that I need ...
1
vote
1answer
80 views

Delete Google Sheet rows matching multiple criteria with custom formula

I have a Google sheet where I am trying to remove multiple rows matching any one of a vector of values using (Filter by condition -> Custom formula). For this example, let's say I want to remove ...