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
0
votes
1answer
28 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
21 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
38 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
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
39 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
39 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
22 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
3answers
52 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 ...
0
votes
3answers
159 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
24 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
36 views

Cell reference for contains value in Google Sheet

Looking for help referencing another spreadsheet, where I want to lookup several values in cell A2, to see if the value is contained in the cell (as there are variations), then setup an else clause at ...
1
vote
2answers
629 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. ...
0
votes
2answers
41 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
2answers
22 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.
1
vote
1answer
26 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....
32
votes
2answers
59k views

FILTER(condition OR condition) syntax?

I am using FILTER() to display a value from Sheet 2 where the Name columns match. My problem is where I want: =FILTER(Sheet2!A:F, Sheet2!A:A="Combat Medic" OR Sheet2!A:A="Universal") as OR results ...
2
votes
1answer
84 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
2answers
58 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
94 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",...
43
votes
7answers
189k views

Google Sheets formula for “if contains”

I'm trying to figure out how to identify IF a list of items in one cell contains a value or string. EXAMPLE Cell A1 contains sites, sheets, docs, slides. I want cell B1 to display a 1 'if' cell ...
1
vote
1answer
34 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 ...
0
votes
2answers
23 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 ...
2
votes
2answers
123 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 ...
0
votes
1answer
26 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
1answer
39 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
2answers
63 views

Can Weekday value be shown in natural (Su, Mo, Tu..) format in Array Formula?

For Google sheet with the following data in A2:B4 06/29/2019 1:25 PM 1 06/30/2019 1:32 PM 1 07/01/2019 1:40 PM 1 The output will show Monday 1 1 Saturday 1 1 Sunday 1 1 Can this be ...
1
vote
1answer
63 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
2answers
989 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 ...
0
votes
1answer
126 views

How to ignore certain strings when creating unique list of words in Google Sheets?

In Google Sheets, I am using the formula to create a unique list of words: =unique(transpose(split(ArrayFormula(concatenate(B2:E"+")),"+"))) But I want to filter out certain strings. My unique list ...
0
votes
1answer
463 views

How to TEXTJOIN in Google Sheets

I have ten columns in my rows which contain 5 sets of first name/last name. I want to combine each of these names in a final column using TEXTJOIN (or whatever function is appropriate if I'm missing ...
1
vote
1answer
23 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!
0
votes
1answer
151 views

Convert Number and Text to Number

I have a spreadsheet in Google Sheets with data like this: In sheet 2, in column pricelist, the below data is there. I want the summary in sheet 1 to have the same. I have used index and match but it ...
1
vote
3answers
111 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
1answer
125 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 ...
0
votes
2answers
20 views

How to work over rows from all other sheets in Google Sheets?

I have a 'summary' sheet and 12 other sheets named 'Jan20', 'Feb20', etc. In the summary sheet I have this formula (to calculate Uber costs): =FILTER('Jan20'!F2:F, REGEXMATCH('Jan20'!D2:D, "UBER")) ...
0
votes
2answers
41 views

How to export CSV without commas in unpopulated cells?

I have a form converter which simply rearranges columns. I use a script to paste Values only into another sheet for export and uploading onto another website. However, when I go to export, it seems ...
0
votes
2answers
39 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 ...
0
votes
1answer
111 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
355 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 ...
1
vote
1answer
15 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
18 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
86 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
48 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
36 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
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
15 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
30 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
19 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
63 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 ...
9
votes
5answers
37k views

Count the number of words in a string in Google Sheets

I have a simple Google spreadsheet with a number of text strings. I just want the number of words contained in each cell. Is there an in-built Google spreadsheet function I may use?