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.
176 questions
0
votes
0
answers
18
views
How can I search for messages in Slack channels that follow a certain pattern?
E.g., I have 3 Slack channels:
#hello-blue-world
#test-blue-case
#this-blue
I have other Slack channels that don't contain blue in their names.
How can I search for messages in Slack channels that ...
- 19.2k
1
vote
1
answer
34
views
How do I set up conditional formatting for values that contain text from a cell in another tab?
From similar problems I found online, I've tried a number of regexmatch and combined it with indirect but nothing seems to work.
Here's a sample of my sheet: https://docs.google.com/spreadsheets/d/...
0
votes
1
answer
40
views
How do I use REGEXTRACT to return grouped numbers from a string where the number of groups varies?
In Google Sheets, I'm trying to return numbers from within a text string.
The numbers are values of the property named either item_id or item_ids.
I've tried the REGEXTRACT function but I'm struggling....
2
votes
1
answer
109
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 ...
- 31
-1
votes
1
answer
146
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&...
2
votes
2
answers
2k
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 ...
- 383
3
votes
1
answer
239
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:
&...
- 33
0
votes
3
answers
144
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
395
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 ...
- 49
4
votes
1
answer
940
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 ...
- 49
1
vote
1
answer
462
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
2
votes
1
answer
137
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
0
votes
1
answer
39
views
How to separate YouTube chapters text to columns and rows in a spreadsheet?
Some YouTube videos have a list of chapters in their description. How can these chapter descriptions be split or separated into columns and rows? Usually the format in the description is as follows:
...
- 109
2
votes
1
answer
421
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 ...
- 141
1
vote
1
answer
27
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
363
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.
...
0
votes
1
answer
339
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
348
views
Regex to update URLs missing HTTPS
I'm looking for a regex or another approach to prepend HTTPS to any existing URLs without it from a list in Google Sheets as well as any that are added in the future.
For example, domain.com would ...
- 67
2
votes
2
answers
2k
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
262
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-...
- 77
0
votes
1
answer
551
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
68
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 ...
- 107
0
votes
1
answer
472
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 &...
- 77
2
votes
0
answers
1k
views
Can I use advanced regex pattern search in Google search? [duplicate]
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 ...
- 21
0
votes
1
answer
196
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
263
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
600
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 &...
- 7,059
0
votes
2
answers
442
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
425
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
117
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
378
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
197
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
2k
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
0
votes
1
answer
32
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 ...
1
vote
1
answer
297
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
889
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"}...
- 473
2
votes
1
answer
141
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
429
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 ...
- 383
2
votes
2
answers
237
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
596
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
6k
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
340
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
43
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
878
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
2
votes
1
answer
4k
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 ...
- 383
1
vote
2
answers
168
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
182
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
312
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