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.

Filter by
Sorted by
Tagged with
1
vote
1answer
27 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....
1
vote
1answer
73 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 ...
1
vote
1answer
27 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 ...
1
vote
1answer
22 views

How can I have Google Sheets automatically parse and calculate text that follows a certain pattern?

I have the following sheet: https://docs.google.com/spreadsheets/d/1iEMjWVEwyuqbn_imW1UZ-AF2NlE__IbRZdHQifKJjko/edit?usp=sharing I would like to calculate "Total Work" by deriving it from my own ...
1
vote
1answer
76 views

Convert cell containing days of the week in first-letter format to day names in Google Sheets

I created a schedule of events in Google Sheets. I have a cell that contains MWF, indicating that the event is every Monday, Wednesday, and Friday. I have a different cell that reports the word ...
1
vote
3answers
315 views

Google Sheets - REGEX Challenge

I have a long Google Sheet column of cells with content looking like this: #text: Jacob Zuma @domain: https://www.theguardian.com/world/zuma #text: World news @domain: https://www.theguardian.com/...
1
vote
1answer
534 views

How to regex extract alpha portion immediately after an underscore in Google Sheets?

Using Google sheets, here's what I'm trying to accomplish: I have data that comes in like this: xta_GAM-1a (61095) xta_SM (61096) xta_GAM2 (61156) xta_GAM-1b (61161) AO CPC ALL GAM AO CPC ALL PA AO ...
1
vote
1answer
619 views

Regular expression in Google Form crashes browser tab

I'm using a free text field in a Google Form. Users should be able to enter the name of a product followed by its price, then start a next line for the next product. To validate their entry I want to ...
1
vote
3answers
2k views

REGEXEXTRACT and French accents in Google Sheets

I have the following formula: =REGEXEXTRACT(source!A2,"^[\w\s\À-ÿ]+") where source!A2="Ancien Marché Saint-Jacques (1125, rue Ontario Est)" and I would like to extract Ancien Marché Saint-...
1
vote
1answer
33 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
1answer
18 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 ...
1
vote
1answer
21 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 ...
1
vote
1answer
14 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
101 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
vote
1answer
21 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.
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
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
14 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
2answers
1k views

How can I create a recursive formula in Google Sheets?

I'm making a task tracker in Google Sheets and am trying to implement subtasks. I am trying to create a formula that checks a row's "Parent Task" column, and if there is a parent task, indents the ...
1
vote
1answer
163 views

Arrayformula for running MAX of separate column

I'm trying to use ARRAYFORMULA in Google Sheets to get the max figures from a separate column only up to the row being calculated each time but can't for the life of me figure out how. It sounds more ...
1
vote
3answers
118 views

Create a regex for Google Analytics, to tack a specific US phone number that can be in multiple formats

I want to write a regular expression to tack a number in Google Analytics for a specific US phone number that supports the following formats: ###-###-#### (###) ###-#### ### ### #### ###.###.#### ...
1
vote
2answers
781 views

Using Google Sheets to bulk collect the number of Google Search results for given keywords

I would like to create a Google Sheets that will allow me to paste in a list of 25 or so keywords in column A and would then return the number of search results for each of those terms in column B. ...
1
vote
0answers
469 views

Search Engine that Doesn't Ignore Punctuation? [duplicate]

Possible Duplicate: How to search the internet for terms with special characters Is there a search engine that will not ignore punctuation marks (like $ % & ! @ #...)? I want to search the ...
0
votes
2answers
48 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....
0
votes
1answer
78 views

Sort QUERY results to the 4th character of a string

I'm trying to pull a list of people based on their last names. The catch is I only want to QUERY people with last names that fit between an alpha range to the 4th character. Example I only want ...
0
votes
1answer
30 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
60 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 ...
0
votes
2answers
57 views

Google Sheets Conditional Formatting

I am trying to make a certain cell on my sheet light up based on what week it is. Each cell contains the value Week X where the X is a week-number. After a little bit of experimentation I have made ...
0
votes
2answers
529 views

Data Validation in Google Sheets Using Multiple Criteria

I have a column with text in the format: [text:more text/numbers] I want to ensure whatever is entered into the cell conforms to this syntax, or at least as much of it as possible. text is always 5 ...
0
votes
1answer
72 views

Remove all text before (and excluding) match

Hi I have a cell with data such as: Dropped pin Near Hughanden Village Hall, Pome Ln, Hughanden, Low Slokey, HT11 3AT https://goo.gl/maps/dVtKw423RBA9fkGs7 I would like to use REGEXREPLACE (or one ...
0
votes
1answer
49 views

Is there a way to search the web by using regex? [closed]

I want to study and find English sentence examples. For example, I read some I'd be working... I want to know the usage of I'd be doing... So I want to search I'd be *ing I'd like to see the ...
0
votes
1answer
424 views

Google Docs copy paste formatting / indent question

So say I am taking a YouTube transcript, and copying the whole thing, and then pasting it into a document. The way the transcript is set up with YouTube it's layered like every 5 seconds so its lines ...
0
votes
1answer
126 views

Split rows dynamically based on row length (COUNTA) and transpose to 2D array

I have data in this format: And I need it to be reformatted into this: Basically, each row in the initial format is composed of 4 concatenated sections of equal lengths: Part(s), Product(s), Quant(s)...
0
votes
1answer
600 views

Regex compare two strings

I have two strings "*j?a?c?k*c?o?o?p?e?r*a?v?o?n*l?a?k?e*o?h*" and "*j?a?c?k*c?o?o?p?e?r*t?r?a?n?s?p?o?r?t*a?v?o?n*l?a?k?e*t?e?r?m?i?n?a?l*" These seem like they should be equal in regex. However, ...
0
votes
1answer
5k views

How to manipulate text strings in Google Apps Scripts

I've found this super userful code that I have managed to add to a Google Sheet (total newbie here) but I have a couple of problems. The first is to disregard blanks. I want to select a list, get ...
0
votes
1answer
302 views

Google Analytics Exclude URI filtering

I have a tracking code which is currently used on two ORACLE APEX webapplications. (210 and 400). I have made a view in Google Analytics, which contains a filter that should exclude the 400 ...
0
votes
1answer
364 views

Regular Expressions

I am experimenting with Cognito Forms' new feature of text input validation by use of Custom Regular Expressions. I was asked by one of our departments to make the name fields autocorrect so as to ...
0
votes
1answer
4k views

RegExp for data validation

I need a help for regexp writing to validate format: any_positive_integer@any_text,any_positive_integer@any_text, ..... To be exact, "any_positive_integer@any_text" separated by commas and inside, ...
0
votes
0answers
27 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 ...
0
votes
1answer
110 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 ...
0
votes
2answers
26 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
62 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
1answer
42 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 ...
0
votes
1answer
141 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 ...
0
votes
1answer
32 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 ...
0
votes
1answer
22 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 ...
0
votes
3answers
205 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
25 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....
0
votes
2answers
159 views

IF Function with too many arguments

I want to apply this formula =IF(A2= "", "", IF(REGEXMATCH(A2, "Brand"), "Brand", if(regexmatch(A2, "Loans"), "Loans" , if(regexmatch(A2, "Cards"), "Credit Cards", if(A2, "Generic"),"Generic",...