Hot answers tagged

174 votes
Accepted

Google Sheets formula for "if contains"

You can use REGEXMATCH: =IF(REGEXMATCH(A1, "sites"), 1, 0) To explain, REGEXMATCH returns true if and only if the argument is a substring of your string.
user avatar
25 votes

Google Sheets formula for "if contains"

If you, as Ryan Shillington suggests, want to know the number of cells within a range that has "sites" in it, you might try: =COUNTIF(A1:A100;"*sites*") The * acts as a wildcard notion, so it will ...
user avatar
  • 251
21 votes

Google Sheets formula for "if contains"

This returns a #VALUE! error if the search term isn't found: =IF(SEARCH("sites", B2) > 0, 1, 0) This uses the same logic but catches the error and returns a 1/0 for success/failure: =IF(...
user avatar
  • 1,804
13 votes

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

Google Search can return the matches of some simple regular expressions. For example, the search query "(gray|red) (wolf|fox)" appears to be equivalent to ("gray wolf" OR "...
user avatar
11 votes

Google Sheets formula for "if contains"

A solution without REGEX: =if(iserror(FIND("sites",B2)),0,1) capitalization matters and if logic must be reversed, given that answer is true when the string is not found.
user avatar
  • 111
9 votes

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

This formula will do the job: =regexextract(A1;"@(.*)") If no @ is found, the original value is shown. The =regexextract function (documentation) executes the regular expression parameter (@(.*)) ...
user avatar
  • 8,458
7 votes

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

Little hacky, but it doesn't require an extra add on script and will likely cover 99% of your use cases. You can still use capture groups with RegexReplace and reference in the replace text with $1 ...
user avatar
  • 847
6 votes
Accepted

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

I was just looking for a similar feature in IFTTT, but then discovered that Zapier (kind of) supports this. They're not proper regex's but it does pattern matching of sorts https://parser.zapier.com ...
user avatar
6 votes
Accepted

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

I would use the following formula to obtain an URL. Formula =IF(ISEMAIL(A2), REGEXEXTRACT(A2,"@(.+)"), IF(ISURL(A2), A2, IF(ISURL(REGEXEXTRACT(A2,"@(.+)")), REGEXEXTRACT(A2,"@(...
user avatar
6 votes

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

Short answer Instead of using the built-in find and replace function use Google Apps Script or an add-on. Explanation On the Find and Replace feature of Google Documents, the Replace part doesn't ...
user avatar
  • 41.6k
6 votes
Accepted

Rearrange Name Format in Google Sheets using one formula only

Delete everything from B:B (including the header) and try this formula in B1: =ArrayFormula({"Reformatted Name";IF(A2:A="",,TRIM(REGEXREPLACE(REGEXREPLACE(A2:A,"Jr$|Jr.$|Sr$|...
user avatar
  • 4,402
5 votes

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

If your email address (bob@example.com) is in A1 =index(split(A1,"@"),0,2) = example.com =index(split(A1,"@"),0,1) = bob You are splitting on the @ and than using index to select which column you ...
user avatar
5 votes

Finding and removing periods

You just need to use the backslash to escape the period character, so you have one of two options: for regexextract to capture everything after the period, thus ignoring everything before it: =...
user avatar
4 votes
Accepted

Appending text to each column in Google Sheets

Please try: Find: (.+) Replace with: $1-banner.jpg Search: This sheet Search using regular expressions Replace all.
user avatar
  • 16.6k
4 votes

Google Sheets formula for "if contains"

This will return TRUE or FALSE: =IFERROR(SEARCH("keyword",A1)>0, FALSE)
user avatar
4 votes

How do I use regexmatch inside sumproduct?

Please try: =sumproduct(($J$6:$J)*($A$6:$A="Order")*(regexmatch($G$6:$G,"T8")))
user avatar
  • 16.6k
4 votes

Matching a cell value to a category from patterns table

Contrary to what I had concluded earlier, "where 'string' matches B" works: Description Expected Category Inferred Category bla 1 cat1 =query(A$1:B$3, "select C where '" & ...
user avatar
  • 160
4 votes

Using regex to select the text between two characters IF there is text

Change your * to a +, so you end up with ">([^<]+)<. I don't think you want the double quotes at the end either, since Account is followed by </div>, not by something like <&...
user avatar
  • 2,068
3 votes

Google Sheets formula for "if contains"

You're probably doing this because you want to know the count or average # of rows that have the word "sites" in it. Assuming you have 100 rows of entries in column A, you can use this function to ...
user avatar
3 votes

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

Just a guess, but OP may want something like this: =query(A:A, "Select A where A matches '.*/[A-Z]+/.*' ") to select from ColumnA only cells containing nothing but Latin alphabet upper case ...
user avatar
  • 16.6k
3 votes

Count the number of words in a string in Google Sheets

Slight improvement to formula in this other answer so in case that A1 is blank or if it has an empty string it will return zero. =IF(LEN(A1)=0,0,COUNTA(SPLIT(A1," "))
user avatar
  • 41.6k
3 votes
Accepted

Is it possible to search MediaWiki sites using regular expressions?

It's available and powered by the CirrusSearch (based on ElasticSearch) which is the used search engine in the MediaWiki sites, and the Wikimedia platform. Some usage examples: Note: Full guide ...
user avatar
  • 3,684
3 votes

Google Sheets formula for "if contains"

If you go with the asterisks option and want a cell reference, you can use the CONCATENATE function to add the asterisks =COUNTIF(A1:A100,CONCATENATE("*",A1,"*"))
user avatar
3 votes

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

As stated by user in the comments Note: Capture groups only work with Google Sheets.
user avatar
3 votes

How can I use the capturing group in a function?

You could try splitting this into two parts returning the latter part as upper. Not sure how to nest the upper inside the regex in Gsheets. =REGEXEXTRACT(A1,"stri") & upper(REGEXEXTRACT(A1, "(...
user avatar
  • 240

Only top scored, non community-wiki answers of a minimum length are eligible