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
1k 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 ...
0
votes
1answer
456 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
2answers
3k 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
501 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 ...
0
votes
1answer
208 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
3answers
145 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
668 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
34 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
273 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
1answer
23 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 ...
0
votes
1answer
54 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 ...
98
votes
8answers
492k 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
votes
1answer
20 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. ...
0
votes
1answer
16 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
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 ...
0
votes
1answer
25 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
1answer
46 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"...
1
vote
1answer
17 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 ...
0
votes
2answers
324 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
3answers
807 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 ...
1
vote
3answers
29 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 ...
0
votes
0answers
20 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
167 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, ...
0
votes
2answers
68 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
1answer
68 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 ...
1
vote
2answers
1k 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
80 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
328 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",...
0
votes
1answer
129 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"}...
0
votes
2answers
153 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
1answer
58 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
54 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, ...
1
vote
2answers
36 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 ...
0
votes
1answer
26 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 ...
2
votes
1answer
66 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 ...
2
votes
1answer
764 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 ...
2
votes
3answers
790 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
2answers
4k 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 ...
3
votes
3answers
86 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
1answer
53 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, (...
3
votes
1answer
125 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&...
3
votes
1answer
38 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(...
1
vote
1answer
24 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,...
72
votes
9answers
119k views

Is there a way to search in Google using regular expressions (regex)?

It would be very useful if Google provided a regular expression search. Is there a way to do this? (OBS: I am not talking about false regular expressions like site:, filetype:, AND, OR or "Text". I ...
2
votes
2answers
65 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
138 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
59 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
48 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
1answer
345 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 ...
1
vote
1answer
93 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 ...