Questions tagged [regex]
regex stands for "regular expressions". They are used to define a string search pattern that could be used in some web applications like Google editors to do input validation, search and replace among other similar tasks.
168
questions
1
vote
0
answers
8
views
Using wildcards in gmail (trying to auto block spam emails that have no unsubscribe button)
About a year ago, I started getting emails form very random email addresses that are just extremely obvious to be spam. However, just blocking one single user account doesn't do the trick because the ...
- 111
1
vote
1
answer
14
views
How do I force csv format in a short answer on a Google form?
so I'm trying to make a google form where a person will need to input either one single number or a series of numbers in a short answer, while necessarily using the comma separators between the ...
- 11
0
votes
1
answer
57
views
ArrayFormula and Categorize Data in Google Sheets using Regular Expressions
I'm following this post to categorize data in Google Sheets using regular expressions.
The person who wrote the answer was able to get the formula to automatically write down the category in column D.
...
2
votes
1
answer
39
views
How to exclude all terms except one word in title when searching YouTube or Google?
That is, how to find one-word-titled webpages in Google search results or in YouTube search results. For example, when searching YouTube for the word ‘verb’, I want only videos with the word ‘verb’ as ...
- 21
0
votes
1
answer
51
views
Remove all Array_Constrain(ArrayForumla(*),1,1) formulas when importing from Excel to Google Sheets using RegEx
My first question here. I have an Excel Spreadsheet, where I have not used CSE, which I import into Google Sheets. Upon importing, some 40k+ cells have =Array_Constrain(ArrayFormula(*),1,1) in them. ...
0
votes
1
answer
41
views
Google Sheet Regex URL Replace
Looking for regex or another solution to update all the URLs without HTTPS at the start of the domain; for example, domain.com to > https://domain.com.
Please, note I have multiple domains in the ...
- 59
1
vote
2
answers
297
views
RegEx on Google Forms: How to make Case Insensitive work?
I have been wondering, how to use case insensitive regular expression for a response validation in a form where there are different possible answers?
I have tried:
(name1|name2|name3)
and it really ...
- 55
4
votes
1
answer
153
views
Rearrange Name Format in Google Sheets using one formula only
Is there a simple way to do this using only one arrayed formula?
Here are the names listed in the above image:
NAME
dela Cruz, Antonio Karl Jr.
Woods, Oliver
Hasegawa, Ken Sr.
Sta. Maria, ...
- 55
0
votes
0
answers
37
views
Issue using Trim and RegExReplace to grab hashtagged terms from text entries
I am new to the more complicated functions of Excel and Google Sheets.
I am trying to extract hashtagged words from my journal entries to get the gist of what those entries are about.
I had pretty ...
- 1
0
votes
1
answer
18
views
Flexible formula to match strings in 1 row to run a formula in matching columns in another row
I have a row where all strings have the identical format (XX-#####-##) in which Xs are 2 upper case alphabet letters and the #s are integers.
Short example, see the doc for more
KC-30000-00 | KC-30000-...
- 51
0
votes
1
answer
263
views
Avoid keeping adding numbers in headings in Google Docs
I am using a modified version taken from this useful answer to get numbers in my Google Docs Header 2:
function myFunction() {
var pars = DocumentApp.getActiveDocument().getParagraphs();
var ...
- 103
0
votes
1
answer
27
views
how to use regextract to find occurences of a number when it ends with a 2 after decimel point
my number set looks like this example
-360.132,
125,
140.0152,
-400.13,
i would like to be able to have an if statement for the numbers that end with a "2" after the decimel point. So i want ...
- 63
0
votes
1
answer
146
views
Google Sheets Replace REGEXMATCH with REGEXEXTRACT
I am working with a Google Sheet where column A contains a set of values. Every value contains the following string "SAME_WORD LETTER", where LETTER is one of the alphabet letters, like &...
- 51
1
vote
0
answers
448
views
Can I use advanced regex pattern search in Google search?
I tried to find results that match pattern such as Apple\s*,\s*[a-zA-Z]+ but it only returned unnecessary data like regex or etc..
I needed only results like such as "Apple, Samsung".
Maybe ...
- 11
0
votes
1
answer
22
views
REGEXMATCH for single cell min/max character criteria?
I'm very new to the regex scene so forgive my level of intelligence! I want a single cell to be 5 or 7 characters long via data validation and an error message for a false return.
I've come up with ...
- 3
0
votes
1
answer
893
views
Problem with invalid regular expression missing / (slash)
I have a spreadsheet. I want to send emails from the Apps Script.
The logic to send email as follows.
Only send 1 email to each unique value in Column A (Co Name) the content of the email will include ...
2
votes
2
answers
173
views
Formula to count the number of different letters in a word
In sheets, suppose cell A1 contains the word CABBAGE.
I want to put a formula into, say, cell B1 that will count the number of different letters. So the output should be 5, because there are 5 ...
- 137
0
votes
2
answers
189
views
Can't format hardkey date data as a date in Google sheets?
I've got some date data in a Google drive sheet which is imported from a CSV.
The date data is formatted as either dd/mm/yy or dd.mm.yy as hard key data (eg. not formatted as a date)
I got to format &...
- 6,283
0
votes
2
answers
155
views
Extracting 2nd occurrence of a number in brackets / Google Sheet/Excel
I currently have cell that has the following output from my Google Sheet:
Share Price vs. Fair ValueBelow Fair Value: AAPL ($126.85) is trading above our estimate of fairvalue ($118.02)Significantly ...
Google Sheet
0
votes
1
answer
177
views
REGEX in Google Sheets with Filter
I'm trying to mix regex with a filter in Google Sheets. What I have at the moment is
=filter(AD:AD,X:X=X2,right(Z:Z,1)={1,2,3,4,5,6,7,8,9})
The regular expression is that the final character in ...
-1
votes
1
answer
61
views
How should I go about populating a Google Sheet with website metadata?
I am creating a table in Google Sheets that will allow me to aggregate various metrics that are relevant for a web-based company. The website has about 3000 pages, most of which are 1000+ word blogs. ...
- 1
0
votes
1
answer
131
views
Count if contains yes using array formula for Google Form entries
I want to count every yes per submission of each Google Form, so the row.
Some of the responses have comments after the yes and it won't count those. This is the formula I am using:
={"Score"...
- 15
1
vote
3
answers
44
views
Count instances of character in a column
Is there a way to count instances of characters down a column, without creating further columns that do row-by-row counts of each character?
Below is an outline of what I'm trying to do.
Note that no ...
- 117
0
votes
1
answer
22
views
How to extract desired text from a cell
As you can see below I have these data in single cell I want only car nos as my result
0
votes
1
answer
894
views
Using UrlFetchApp.fetch(url) with regex to extract website data
I'm trying to extract data from a list of >1000 URLs using a script that uses UrlFetchApp.fetch(url) and regex based on this article.
This is the code I'm using.
function importRegex(url, ...
- 145
1
vote
1
answer
26
views
How to create an average value for every cell which contains a certain phrase?
I need to find the average value of cells containing a certain set of initials to create a scoreboard.
The value of the cell being the score and the brackets containing the initials of the score ...
- 11
1
vote
1
answer
165
views
How to filter blanks in a table using ARRAYFORMULA
I want to copy down filter formula using Arrayformula. I also gave a hand to copy down add-on but it's not giving required result. Here is a picture of an example sheet:
I am filtering the non blank ...
- 15
0
votes
1
answer
621
views
Arrayformula function keep getting error different sizes
I'm trying to sum rows with specific criteria with the following formula:
=ArrayFormula(sum(countifs(disposition,"Consultation" ,Primary,{"","OB_Gyn","Surgical"}...
- 397
2
votes
1
answer
85
views
How to split two-line rows into separate rows
Editable spreadsheet here:
https://docs.google.com/spreadsheets/d/1cHf9zC00LymZgb17J746ZUoD-YRG1UvIUQQXVlovBrE/edit?usp=sharing
You can see each non-blank row contains two lines. I'm trying to find a ...
- 23
3
votes
3
answers
135
views
Find or conditional format a column of phone numbers NOT in desired format
I have a column of phone numbers in that are in different formats. I want them all to be in this format 0000 000 000.
What I do now is manually edit each wrong number into the correct format.
How can ...
- 191
2
votes
2
answers
51
views
Spreadsheet formula to calculate most common items in ordered items lists
I am searching for a correct google spreadsheet formula to go through the list of ordered items and summarise the resulting winning items.
Like is you have an Oscar ceremony and you get votes from ...
- 163
1
vote
1
answer
232
views
How to filter Queried Data in an Array
Sample Spreadsheet: https://docs.google.com/spreadsheets/d/16p4M5UBODyBhmWFXYrA0FdLsA1FmwSmbrF12VUHlGaw/edit?usp=sharing
I have this formula which works great:
=ARRAYFORMULA(QUERY({Projects!A:C, (...
- 155
1
vote
2
answers
4k
views
Google Sheets for if contains "*"
I have found the the regexmatch solution for substring: Google Sheets formula for "if contains"
However, why for "*" is not working? I'm trying to make a condition if the name ...
3
votes
1
answer
239
views
Using regex to select the text between two characters IF there is text
I have this text:
<a class="_f5rco33" href="/account-settings"><div class="_ojs7nk">Account</div></a>
And I want to grab the word "Account&...
gbutton
1
vote
1
answer
29
views
Replacing items in comma delimited cell with same string each time
I'm trying to create a formula that will replace any contents of comma delimited cell with single string. The number of items in delimited cell will vary...
Specifically:
524352435,2345235523,...
- 153
2
votes
2
answers
134
views
How do I find the max value in a column only if it contains certain texts in google sheets?
The data has numbers in text format and texts with numbers.
How do I find the max of the numbers in text format without converting to numbers?
How do I find the max if it contains certain texts (ie, ...
- 21
1
vote
1
answer
2k
views
Use Regular Expressions to Add Text to Beginning or End of Cells
I have a bunch of words in a sheet in one column, e.g.:
Study Guide 101
Meditation for Beginners Cheatsheet
Lose Weight Fast
What I want to do is add a word to the end of these in the cell using ...
- 191
1
vote
2
answers
72
views
Google sheets, convert value from 1 column to another based on particular data in it
Let's say I have those values in the column:
w 1
w 2
w 3
I want to convert those values to
1
2
3
in another column.
So basically I need to convert the value "w x" to "x".
How ...
- 13
-1
votes
1
answer
222
views
Regular expression for csv of numbers less than 1,000
I am going to use JavaScript to validate a string that needs to be:
Only digits and commas
Can't end or start with a comma
Each number between the commas has to less than 1,000
No negatives
No ...
- 1
1
vote
2
answers
89
views
In Google Sheets, analyze cell content, and if certain values are present, return a response in the next column
In the below example, I want to analyze each cell in column A, and if the cell's contents match certain parameters, return a value in column B accordingly.
So if the value in a col A cell = apple, ...
- 11
0
votes
1
answer
82
views
How to assign country based off list of either provinces or states?
I have a list of names and locations. Most of the locations include the name of the province or state. Some only include country names, some include state abbreviations.
I also have a list of ...
- 1
1
vote
1
answer
460
views
Applying a custom sort to Google Sheets string column
I am trying to sort a table of rows by their first column that is comprised of strings with this format (capital letters followed by a dash followed by numbers).
Using the default string sort I get ...
- 121
-1
votes
1
answer
580
views
How do I use non capturing groups in google sheets?
This is what I intend to do.
But a similar one doesn't work.
As with any problem I post, I still can't solve it after more than two hours of trying. :-(
https://github.com/google/re2/wiki/Syntax
- 111
1
vote
2
answers
828
views
Extracting text from a capture group while using an arrayformula to consider multiple criteria
I have a page which contains a list of regular expressions in the format "some text (thing I want) but only if (thing is matched here)" that are fairly varied. There are 29 of them so far, ...
- 111
1
vote
1
answer
290
views
Regular Expression to wrap phrases between commas with carets (^)
I have a Google Sheet with example information like this
Education, Conservation, Health, Human rights, Rural development, Poverty alleviation, Water and sanitation.
--------------------
Human rights, ...
- 13
0
votes
0
answers
502
views
Extract data from web with Google script
I'm trying to use the function below (source) to avoid using importxml in Google Sheets:
function importRegex(url, regex_string) {
var html, content = '';
var response = UrlFetchApp.fetch(url);
...
1
vote
1
answer
390
views
Convert string to real date time value?
Here is my current input:
14.05.20
12.37.01
And yes, as it shows, there is a line break in the cell...
Here is example sheet: https://docs.google.com/spreadsheets/d/...
- 638
0
votes
2
answers
1k
views
How do I replace a paragraph break (i.e. Enter) with a Shift+Enter character in Google Docs?
That is, what regexes do I place in these two boxes
I have read Syntax for Unicode characters in regexes in Google Sheets? to no avail.
I found from one answer that [\n] matches paragraph mark. Now ...
- 103
0
votes
1
answer
360
views
Conditional Find and Replace in Google Sheets
Example data:
A B
------
1 HELLO
2 HELLO
2 HELLO
3 HELLO
I want to replace $ with , WORLD! but only if A is equal to 2. Such that the final result would be:
A B
------
1 HELLO
...
- 111
1
vote
1
answer
2k
views
How to allow users to enter only text in short questions in Google Form
I want users to enter only text in short questions in a Google Form.
Like, if I ask user his name then he is allowed to enter only text.
No digits or special characters.
- 11