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.
175
questions
1
vote
1
answer
13
views
Check whether a value appears in one of five columns in another sheet and return a specified value depending on which one it's in
I'm really tired and can't remember how to make this work so would appreciate any pointers on where I'm going wrong.
I'm trying to do the following:
Get cell K2 to provide a specified text value ...
- 21
-1
votes
1
answer
33
views
How do I filter text in a column in google sheets?
I have a column that contains text and numbers in the same cell.
I want to return all the rows from a column that contain both the number 1 and the number 2.
I tried using =regexmatch(E1,"1|2&...
1
vote
2
answers
343
views
Google Sheets Conditional format when cell contains any word from another cell (partial text match)
I am currently using =regexmatch(A1,"(?i)N02|N22|J25") in column A to highlight the cells that contain these terms.
But I don't want to hardcode it as the search terms keep changing.
The ...
- 343
2
votes
1
answer
78
views
Extracting hashtags from a cell - fixing a 99% working regex formula
I'm trying to use this formula to extract hashtags from a cell. I got it from this very old topic.
=trim(regexreplace(A2, "((^|\s)[^#]\S*)|([^#\w\s]\S*)", ""))
Example source:
&...
- 23
0
votes
3
answers
40
views
Filter formula: Exclude data without a timestamp
=sort(
filter(
{ Bryson!A4:S502; David!A4:S502; Justin!A4:S502; Michael!A4:S502; Manny!A4:S502; ScottC!A4:S502; ScottD!A4:S502; Zhenqian!A4:S502; Visitors!A4:S502 },
regexmatch({ Bryson!Q4:...
0
votes
1
answer
120
views
How would you find and extract the 2nd ocurrence and extract only the number that is in between =value"123456789"?
Any expert on Google Sheets Regular expressions? I am trying to extract the numbers that exist in the second occurrence of this pattern value="400" from the HTML text that I am sharing in ...
- 39
3
votes
1
answer
186
views
Find the words after the last symbol
Any expert on Google Sheets Regular expressions? I am trying to extract all the text after the last two dots symbol found :
Example on the text= Roger : Blue Large : pack : high brand the word that I ...
- 39
1
vote
1
answer
171
views
Google Sheet Check if any values in a column match part of a cell
I'm wanting to create a list of strings to search for in Column A. Example "Cafe", "Restaurant", "Shop X"...
Then if a string in another cell (example: "Robert ...
- 113
1
vote
1
answer
89
views
Using Google Sheets to bulk collect the number of Google Search results count for given keywords
I need to bulk check the number of Google Search results count for given keywords by using Google Sheets or Microsoft excel
For example,
ocean is About 2,960,000,000 results
1
vote
0
answers
196
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 ...
- 131
1
vote
1
answer
19
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
264
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
297
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
156
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
323
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
2
votes
2
answers
1k
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 ...
- 65
4
votes
1
answer
188
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, ...
- 65
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-...
- 65
0
votes
1
answer
470
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
50
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 ...
- 99
0
votes
1
answer
280
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 &...
- 65
1
vote
0
answers
1k
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
93
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
2k
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
186
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
458
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,785
0
votes
2
answers
289
views
Extracting 2nd occurrence of a number in parenthesis
I have the following text string in Google Sheets, DV2:
Share Price vs. Fair ValueBelow Fair Value: AAPL ($126.85) is trading above our estimate of fairvalue ($118.02)Significantly Below Fair Value: ...
Google Sheet
0
votes
1
answer
246
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
84
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
207
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
79
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
29
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
1k
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
29
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
250
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
732
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"}...
- 457
2
votes
1
answer
113
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
237
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 ...
- 343
2
votes
2
answers
69
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
529
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
5k
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
279
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
32
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
321
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
3k
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 ...
- 343
1
vote
2
answers
123
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
vote
2
answers
129
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
199
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
696
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
907
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