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.
- 4,591
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 ...
- 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(...
- 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 "...
- 2,362
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.
- 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 (@(.*)) ...
- 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 ...
- 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
...
- 76
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,"@(...
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 ...
- 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$|...
- 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 ...
- 51
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,591
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.
- 16.6k
4
votes
Google Sheets formula for "if contains"
This will return TRUE or FALSE:
=IFERROR(SEARCH("keyword",A1)>0, FALSE)
- 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")))
- 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 '" & ...
- 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 <&...
- 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 ...
- 131
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 ...
- 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," "))
- 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 ...
- 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,"*"))
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.
- 139
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, "(...
- 240
Only top scored, non community-wiki answers of a minimum length are eligible