123
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.
18
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(IFERROR(SEARCH("sites", B2) > 0, 0), 1, 0)
13
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 "red fox" OR "red wolf" OR "gray fox")
You can see the output of this search query here.
Google Search also allows "wildcard" ...
11
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 look for a match anywhere within the cell, not just an exact match for the whole cell.
I've also done this with a cell reference instead of "sites", but ...
9
In Google Spreadsheets, I would do it a bit different.
Formula
=COUNTA(SPLIT(A1, " "))
Explained
The SPLIT function is only available in Google Spreadsheet and will split the cell's content on every space (" "). The COUNT function will simply count the instances.
The Excel formula gives the same answer, but a bit more laborious:
=LEN(TRIM(A1))-LEN(...
answered Nov 15 '13 at 19:08
Jacob Jan Tuinstra
21.5k16 gold badges91 silver badges184 bronze badges
9
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.
8
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 (@(.*)) against the value of cell A1, and returns the matching group.
7
You can write a piece of software to:
Take the keywords from the regular expression;
Google the keywords and get a list of results;
Crawl each resulting URI and filter it with complete regular expression.
Let's study a case: from site:gog.com find all games that have Spanish voice-over.
The regular expression is:
Audio[^:]*:[^.,]*Spanish
It shall match, ...
6
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
Might be good enough for what you need!
6
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 work with regular expressions and it doesn't work either with the replaceText() method from the Documents Service in Google Apps Script fortunately JavaScript ...
6
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 or $2. Just split up your regex into two capture groups and concatenate with a random (infrequently used) character like ~. Then you can take the entire ...
5
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:
=REGEXEXTRACT(A1,"\.(.*)")
or regexreplace to replace everything including and before the period:
=REGEXREPLACE(A1,"(.*\.)","")
5
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 want from the split.
5
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,"@(.+)"),
"No valid entry"
)
)
)
Copy / Paste
=IF(ISEMAIL(A2),REGEXEXTRACT(A2,"@(.+)"),IF(ISURL(A2),A2,IF(ISURL(REGEXEXTRACT(A2,"@(.+)")),...
answered Oct 21 '14 at 10:25
Jacob Jan Tuinstra
21.5k16 gold badges91 silver badges184 bronze badges
4
Please try:
=sumproduct(($J$6:$J)*($A$6:$A="Order")*(regexmatch($G$6:$G,"T8")))
4
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 <"div which wouldn't be valid HTML anyway.
* means 0 or more matches, + means 1 or more. See e.g. Wikipedia for some information about these 'quantifiers'.
Here ...
3
Please try:
Find: (.+)
Replace with: $1-banner.jpg
Search: This sheet
Search using regular expressions
Replace all.
3
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 letters between two virgulas suspensiva (forward slashes) whether or not in the context of other characters.
3
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," "))
3
Two solutions here:
Google Drive Spreadsheet - Continue with using the speadsheet as linked, but limit the search area to avoid hitting the "Exceeded maximum execution time".
Download via IMAP
1. Google Drive Spreadsheet
It's possible via a Google Drive Spreadsheet.
The original credit for Regular Expression based search of Gmail emails goes to Labnol....
3
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 reference, and source - on Wikipedia: Help:Searching#Search_string_syntax.
As for your question:
Logical operators
The search engine supports boolean logic ...
3
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
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 '" & A8 & "' matches B limit 1")
bla 2 cat2 =query(A$1:B$3, "select C where '" & A9 & "' matches B limit 1")
bla blue 2 cat2 ...
3
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, "(ng)"))
this returned for me:
string > striNG
3
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 unique character ♦ to all values.
Then textjoin joins all data with char(10) >> that's your empty row between sets.
Next a series of regexreplaces are applied ...
Only top voted, non community-wiki answers of a minimum length are eligible
