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.
- 4,751
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 ...
- 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(...
- 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.
- 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 ...
- 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 ...
- 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$|...
- 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.
- 159
5
votes
Google Sheets formula for "if contains"
This will return TRUE or FALSE:
=IFERROR(SEARCH("keyword",A1)>0, FALSE)
- 151
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:
=...
- 4,751
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 ...
- 141
4
votes
How do I use regexmatch inside sumproduct?
Please try:
=sumproduct(($J$6:$J)*($A$6:$A="Order")*(regexmatch($G$6:$G,"T8")))
- 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 '" & ...
- 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, "(...
- 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 <&...
- 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,"*"))
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 ...
- 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 ...
- 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," "))
- 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 ...
- 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
...
- 551
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/(\...
- 551
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 ...
- 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<>&...
- 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.
- 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)
- 26.2k
Only top scored, non community-wiki answers of a minimum length are eligible