Hot answers tagged

183 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.
Aurielle Perlmann's user avatar
30 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 ...
Heidi's user avatar
  • 301
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(...
Tom Woodward's user avatar
  • 1,844
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.
Adrià's user avatar
  • 211
9 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 ...
KyleMit's user avatar
  • 917
7 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 ...
Rubén's user avatar
  • 44.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$|...
Erik Tyler's user avatar
  • 4,980
5 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.
Kelvin's user avatar
  • 159
5 votes

Google Sheets formula for "if contains"

This will return TRUE or FALSE: =IFERROR(SEARCH("keyword",A1)>0, FALSE)
Moshe Quantz's 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: =...
Aurielle Perlmann's user avatar
4 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 ...
Ryan Shillington's 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")))
pnuts's user avatar
  • 17.4k
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 '" & ...
Oliver's user avatar
  • 160
4 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, "(...
Umar.H's user avatar
  • 270
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 <&...
Glorfindel's user avatar
  • 2,250
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,"*"))
Anel Rojas Hernández's user avatar
3 votes

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

Google Search doesn't allow searching for punctuation characters. Further, the asterisk (*) acts as a wildcard character in searches. For instance * tart recipe Should return recipes for apple ...
ale's user avatar
  • 52.3k
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 ...
arieljannai's user avatar
  • 3,884
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," "))
Rubén's user avatar
  • 44.6k
3 votes
Accepted

How to refer to elements of a dynamic array in Google Sheets?

The below formula takes the OP query formula and inserts an extra column with value ":" between 3th and 4th column. then the outer QUERY shmushes all columns in one column and appends a ...
user0's user avatar
  • 26.2k
3 votes
Accepted

Syntax for Unicode characters in regexes in Google Sheets?

There are three ways of matching Unicode characters according to Google Sheets' regular expression documentation: Using exactly two digit hex code: \xA0 Using up to three digits octal code: \240 ...
Cornelius Roemer's user avatar
3 votes
Accepted

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

You have the basic concept right just piece it together step by step. This does the job: =REGEXREPLACE(JOIN(" ",ARRAYFORMULA(IFERROR( REGEXEXTRACT(SPLIT(A1," ",TRUE,TRUE), "https://example.com/(\...
Cornelius Roemer's user avatar
3 votes
Accepted

Convert string to real date time value?

Edit In your Google thread you mention: Would really like to be able to convert this in one formula/step. ... I would love if it if could handle the whole column down. In this case, you can use the ...
marikamitsos's user avatar
  • 12.6k
3 votes

Find or conditional format a column of phone numbers NOT in desired format

You mentioned How can I use a filter or conditional formatting to find all numbers that are NOT in this format (#### ### ###). Please use the following conditional-formatting rule =IF(A1<>&...
marikamitsos's user avatar
  • 12.6k
3 votes

RegEx on Google Forms: How to make Case Insensitive work?

Use a non-capturing group to set flags, like this: (?i)(name1|name2|name3) See RE2.
doubleunary's user avatar
  • 13.2k
2 votes

FILTER(condition OR condition) syntax?

=FILTER(A:F, REGEXMATCH(A:A, "Combat Medic|Universal")) =QUERY(A:F, " where A matches 'Combat Medic|Universal'", 0)
user0's user avatar
  • 26.2k

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