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.
147
questions
0
votes
0answers
9 views
Google sheets: Regexextract next or previous whole word or number from cell in sheets
How do I extract the next word or number (including comma and delimiter) to a search string from a cell in sheets?
And how the previous?
={$AV$14;
ArrayFormula(IF(ISBLANK($AT$2:$AT),,
iferror(
value(...
0
votes
1answer
12 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
1answer
34 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, ...
1
vote
1answer
22 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
1answer
48 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 ...
0
votes
1answer
51 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"}...
2
votes
1answer
59 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 ...
3
votes
3answers
81 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 ...
1
vote
2answers
33 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 ...
1
vote
1answer
50 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, (...
0
votes
2answers
124 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
1answer
86 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&...
1
vote
1answer
23 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,...
2
votes
2answers
64 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, ...
1
vote
1answer
64 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 ...
1
vote
2answers
58 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 ...
-1
votes
1answer
43 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 ...
0
votes
2answers
53 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, ...
0
votes
1answer
25 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
vote
1answer
59 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 ...
0
votes
1answer
164 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
1
vote
2answers
160 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, ...
1
vote
1answer
32 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, ...
0
votes
0answers
98 views
Regular expression in Google Forms Quiz Answer Key
Google Forms has the ability to create quizzes with an answer key containing possible correct answers. For instance, if you wanted to forgive misspellings, then your answer key could include "...
0
votes
0answers
168 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
1answer
67 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/...
0
votes
2answers
183 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 ...
0
votes
1answer
65 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
...
1
vote
1answer
565 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.
1
vote
1answer
32 views
How to clear a cell if there is no data in it
How to clear a cell if there is no data in it?
=ArrayFormula(REPLACE(IMPORTRANGE("1I1MK51uYT_WJDh0fiiHxLjnYMoQOVoZi41XnHM", "SetstudentMatrik!C2:C"),1,6,"******"))
0
votes
1answer
261 views
How to extract time out of text after specific word in Google Sheets with regex function
I hope someone can help me with my struggle. I need to get the time slots after each "Text_Text" from this one sentence that is in a single cell:
Sth 03/30/2020 1:15 PM-03/30/2020 1:30 PM;"Text_Text" ...
1
vote
1answer
135 views
How to extract time out of text in Google Sheets
I have the following issue and I would appreciate your help. I need to get the time values out of this string:
SomeText 02/02/2020 9:00 AM-02/02/2020 9:15 AM;"Text" 02/02/2020 10:45 AM-02/02/2020 11:...
1
vote
1answer
723 views
Sumif and filter using regexmatch with multiple criteria
I need to sum specific cells in 1 column (Q) based on if the other columns (R, S) have a certain word(s) within the cell.
For example in the picture if a cell in R or S contains the word toy or green,...
1
vote
2answers
75 views
Find and place in a column, the first non-blank cell in each row from a range of columns
I'm attempting to create an ARRAYFORMULA that can extract the first non-blank cell value on each row in a range of columns.
My latest attempt is this:
=TRANSPOSE(QUERY(TRANSPOSE( A2:D6),"select * ...
0
votes
2answers
68 views
Average of only the numeric part of cell values when some of the cells contain text and numeric values
I am trying to figure out a formula that will allow me to find the average of a group of cells, where some of the cells only contain numeric values, and some cells contain both numeric values and text....
2
votes
3answers
524 views
Find and extract words/names from a string beginning with specific character
I've got some rows in Google Sheets that contain strings. I want to extract certain parts from this string and enter them into another cell:
specifically, I want to extract words/names that begin with ...
1
vote
1answer
242 views
Google Sheets formula for “if contains” for grading multiple choice quiz
I want to use a Google Sheets formula to correct a multiple choice survey that I built using Squarespace. The results get dumped into Google sheets.
For example, if the respondent selects choice:
A....
2
votes
1answer
616 views
How to extract multiple paths from a single string of URLs in Google Sheets?
How can I get a subrange of second to last element in a formula under Google Sheets?
I'm trying to extract all occurrences of https://example.com/* in a cell which may contain multiple URLs scattered ...
0
votes
1answer
2k views
In Google Sheets I need to find a string and return true/false, or even a count into a cell so I can perform an action
In my example i have a column with Names of signs we product in our sighshop and I need to be able to isolate similar named signs So say I need to return a a value if the cell is Bud Light and only ...
2
votes
2answers
1k views
Query or Filter formula to 'search for' and 'return' multiple words
I am a basic user of Google Sheets and need help creating a new formula. I am trying to filter a worksheet so that it returns particular information into another sheet.
The filter I have created is ...
2
votes
1answer
501 views
Syntax for Unicode characters in regexes in Google Sheets?
What's the syntax to use for general Unicode characters in regular expressions in Google Sheets?
Use case: When using REGEXREPLACE with user submitted strings in Google Sheets, I have come across a ...
1
vote
1answer
29 views
How do I make the “Text contains” evaluate any word written in the cell?
I just want the cell in Google Sheets to change its fill color when any text is inputted in the cell? But it has to be text excluding numbers. Please help!
1
vote
2answers
74 views
Use Google Sheets to create column C by deleting any phrases that contain word found in column B in column A
I have a list in column A and I want to remove any cells that contain any word.
0
votes
1answer
83 views
Merging cells based on same ID but divided into categories (columns)
I receive a pretty extent list of material that has been delivered and every single product is in a separate row. That means for one order I have easily a lot over 15 rows and I need to merge the ...
0
votes
2answers
127 views
How to filter after parsing CSV values contained in Google Sheets cells
I would like to be able to match all cells in column A (titles) that have one of the comma-separated values in column E (Key Search Terms). This will involve at least two steps. 1) Parse each of the ...
0
votes
2answers
78 views
IF and IFS not working properly
I am trying to locate data present in different sheets. I have tried using IF and IFS.
Below are the formulas I have used. For IF, I get NOT IN USE even when I know there should be one of the sheets ...
3
votes
1answer
36 views
How to convert strings separated by comma from base 36 to decimal
I'm getting input from a google form. An associate scans a QR code giving a string like ^12t34:1
Cell B1 = ^12t34:1
The string between the ^ and : is base36. I can use
C1
=DECIMAL(SUBSTITUTE(...
0
votes
2answers
209 views
Is it possible to have Google Sheets query function that accepts a dynamic query argument instead of hard coding?
I have a long OR query function that I will be reusing many times in many contexts and that may change frequently. Following the principles of DRY design, I would like to write this query once and ...
1
vote
1answer
39 views
Conditional Output on cell based on 2 different “IF(REGEXMATCH” functions
First of all sorry if I am not clear. it is my first question.
I am trying to make a cell give an output with IF(REGEXMATCH on the cell A1 for example
=IF(RegExMatch(A2,"YES"),"Good"
I also need ...
0
votes
1answer
55 views
If cell y contains any word from range x:x, mark cell y green
In Google Sheets, I have three columns (with different lengths!)
Column1 contains firm names (50)
Column2 containts firm names (100)
I would like to mark a cell in Column1 green (or with a number in ...
