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.
126
questions
1
vote
1answer
15 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
19 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
21 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
39 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
24 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
19 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
33 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
30 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
98 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
66 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
213 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,...
0
votes
0answers
55 views
Conditional Formatting on Google Sheets using regex
I am now going in circles, I tried to look for answers here but I am still stuck.
I want to highlight a cell using conditional formatting if the value of that cell is present anywhere in a specific ...
1
vote
2answers
55 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
57 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
108 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
64 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
308 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
652 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
695 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
279 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
27 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
39 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
40 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
52 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
69 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 ...
0
votes
2answers
103 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
31 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
49 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 ...
1
vote
1answer
22 views
Google Sheets forumla help
With my sheet, I need it so that if the job is any type of "Repair" then it minuses 1000 from the business profit but if it's anything else it doesn't minus the 1000 from the business profit. I have ...
0
votes
1answer
227 views
How to detect duplicates across multiple sheets?
I have found a couple of formulas/scripts for what I am looking for, but they are all locked for one sheet only, thus I will try to explain what exactly I need. I am unable to do it my self, because ...
1
vote
1answer
18 views
Highlight data that match with data from other sheet
I have two tabs in a Spreadsheet. Both are having a list of data. I want to highlight cells in one tab that match with cells from another tab.
For example:
Tab 1
Column 1
ABC
XYZ
PQR
DEF
Tab 2
...
1
vote
1answer
76 views
Checking whether two columns contain the same string of numbers
Some time ago I have put together a perfectly working formula but now it's not working and I can't tell why (I may have changed something and don't remember that).
Column B contains data that I need ...
1
vote
1answer
202 views
ImportXML With Line Breaks Google Sheets
I am using ImportXML to get some data from a website which looks like the following:
=ImportXML("https://www.papercall.io/events?cfps-scope=&keywords=uk","//div[@class='panel panel-default']")
...
-1
votes
2answers
48 views
Help with a Vlookup function to return multiple results of not exact match
I am trying to run the following code to return all the matches of column A from column C in a set of rows. In this situation, cell A1, is contained in a few iterations of column C, however, these are ...
1
vote
1answer
22 views
How to display list of items that is in other sheet but not in the other? [closed]
As the title says, I need a column to show what is not on a range(BASIS) that is in the other sheet(BASIS)
:
I've been searching for hours now but only found the opposite results.
0
votes
1answer
36 views
Conditional formatting with multiple names in list
Col A2:C15 contain a list of names. I need to enter those names in various combinations into one of 15 other cells on formatted like:
H3 = John, Steve, Bob, Dan
Is there a way to use conditional ...
1
vote
1answer
34 views
How to search value in another column?
Using Google Sheets, I am trying to two compare two columns on Google Sheets, like the one below:
Basically, I want to compare column A and B, basically, if a cell in column A is inside a cell in ...
1
vote
1answer
195 views
Delete Google Sheet rows matching multiple criteria with custom formula
I have a Google sheet where I am trying to remove multiple rows matching any one of a vector of values using (Filter by condition -> Custom formula).
For this example, let's say I want to remove ...
1
vote
1answer
21 views
How can I return a string instead of a number when the number is 0
I have a formula that returns average lbs, waist and body fat % for the weeks I've been dieting. The problem is I didn't measure body fat % until a while after I started so some averages are 0. I'd ...
1
vote
1answer
42 views
Conversion from LibreOffice to Google Sheets
I use LibreOffice to create xlsx files. There are some formulas to test conditions. Everything is ok under LibreOffice but when I open the file with Google Sheets formulas give the wrong result.
e.g....
0
votes
1answer
146 views
Replacing data in Google Sheets
I am new to coding! I have data in Sheet 1 of a Larger Spreadsheet that looks like this
25.06.18
and wish to change it to look like
26/06/18
I have tried to write a macro:
function Dated() {
...
1
vote
1answer
70 views
Filter spreadsheet by user-entered value in first row
In Google Sheets, I want to set up a filtered view like this.
Row 1 = column headings
Row 2 = filter criteria
Rows 3... = data
I want the user to type a value into any column of row 2, and ...
1
vote
1answer
330 views
How to refer to elements of a dynamic array in Google Sheets?
I'm trying to create teaching timetables dynamically, by searching through a large table with all of the information about what time a lesson takes place, who the tutor is, what age group is being ...
1
vote
1answer
18 views
Split cells not importing first 0
I am using split formula:
=IFERROR(SPLIT(REGEXREPLACE(" , "&U4, "(\d)", "$1 ,"), ", "))
It works great except if I have 0250, it imports it across as 250, even with the cell it's importing ...
0
votes
1answer
23 views
Sort sentences based on formatting
I have a list of sentences in Google Sheets, each word in a different column.
There is a stupid man.
How we came here we don't know anything about.
Finally, it has come to this.
Shaking my head.
How ...
-1
votes
1answer
76 views
How to nullify empty output errors in Google Sheets?
I am using the following code in many cells of the sheet,
=SPLIT(REGEXREPLACE("" & S2, "(\d)", "$1,"), ",")
not all are going to contain data at any one time. How do I stop the empty cell value ...
1
vote
1answer
16 views
Issue with finding the averages of multiple rows at once
I am currently tasked with hundreds of rows of data for a project. I need to take the average of each row, but there are way too many rows for me to do by each individual row. Is there any way to take ...
1
vote
1answer
29 views
Comparing two cells and adding text to matching values
I have two columns, one with all items in a set and another with the items I currently have. I would like to compare those and for the matching results to have a bit of text added if they match.
So ...
0
votes
3answers
332 views
Checkbox to activate a column in QUERY
Is there a way to activate/deactivate a column in QUERY function?
I have a query to query and selection of several columns
Select Col1,Col31,Col32,Col33,Col25,Col26,
and want to have a checkbox for ...
0
votes
1answer
36 views
What function formula should I use to calculate my salary so that I don't have to differentiate between Friday and weekend manually?
These are the formulae I am currently using where I have to manually sort out if its Friday or weekend by using conditional formatting to bring attention to the minority that needs a different formula....
