Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

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
1 answer
27 views

Google Sheet Regex URL Replace

Looking for regex or another solution to update all the URLs without HTTPS at the start of the domain; for example, domain.com to > https://domain.com. Please, note I have multiple domains in the ...
user avatar
  • 49
0 votes
1 answer
40 views

RegEx on Google Forms: How to make Case Insensitive work?

I have been wondering, how to use case insensitive regular expression for a response validation in a form where there are different possible answers? I have tried: (name1|name2|name3) and it really ...
user avatar
  • 45
4 votes
1 answer
130 views

Rearrange Name Format in Google Sheets using one formula only

Is there a simple way to do this using only one arrayed formula? Here are the names listed in the above image: NAME dela Cruz, Antonio Karl Jr. Woods, Oliver Hasegawa, Ken Sr. Sta. Maria, ...
user avatar
  • 45
0 votes
0 answers
35 views

Issue using Trim and RegExReplace to grab hashtagged terms from text entries

I am new to the more complicated functions of Excel and Google Sheets. I am trying to extract hashtagged words from my journal entries to get the gist of what those entries are about. I had pretty ...
user avatar
  • 1
0 votes
1 answer
18 views

Flexible formula to match strings in 1 row to run a formula in matching columns in another row

I have a row where all strings have the identical format (XX-#####-##) in which Xs are 2 upper case alphabet letters and the #s are integers. Short example, see the doc for more KC-30000-00 | KC-30000-...
user avatar
  • 21
0 votes
1 answer
114 views

Avoid keeping adding numbers in headings in Google Docs

I am using a modified version taken from this useful answer to get numbers in my Google Docs Header 2: function myFunction() { var pars = DocumentApp.getActiveDocument().getParagraphs(); var ...
user avatar
  • 103
0 votes
1 answer
24 views

how to use regextract to find occurences of a number when it ends with a 2 after decimel point

my number set looks like this example -360.132, 125, 140.0152, -400.13, i would like to be able to have an if statement for the numbers that end with a "2" after the decimel point. So i want ...
user avatar
0 votes
1 answer
81 views

Google Sheets Replace REGEXMATCH with REGEXEXTRACT

I am working with a Google Sheet where column A contains a set of values. Every value contains the following string "SAME_WORD LETTER", where LETTER is one of the alphabet letters, like &...
user avatar
  • 21
0 votes
0 answers
243 views

Can I use advanced regex pattern search in Google search?

I tried to find results that match pattern such as Apple\s*,\s*[a-zA-Z]+ but it only returned unnecessary data like regex or etc.. I needed only results like such as "Apple, Samsung". Maybe ...
user avatar
0 votes
1 answer
18 views

REGEXMATCH for single cell min/max character criteria?

I'm very new to the regex scene so forgive my level of intelligence! I want a single cell to be 5 or 7 characters long via data validation and an error message for a false return. I've come up with ...
user avatar
  • 3
0 votes
1 answer
490 views

Problem with invalid regular expression missing / (slash)

I have a spreadsheet. I want to send emails from the Apps Script. The logic to send email as follows. Only send 1 email to each unique value in Column A (Co Name) the content of the email will include ...
user avatar
2 votes
2 answers
170 views

Formula to count the number of different letters in a word

In sheets, suppose cell A1 contains the word CABBAGE. I want to put a formula into, say, cell B1 that will count the number of different letters. So the output should be 5, because there are 5 ...
user avatar
0 votes
2 answers
143 views

Can't format hardkey date data as a date in Google sheets?

I've got some date data in a Google drive sheet which is imported from a CSV. The date data is formatted as either dd/mm/yy or dd.mm.yy as hard key data (eg. not formatted as a date) I got to format &...
user avatar
  • 6,143
0 votes
2 answers
118 views

Extracting 2nd occurrence of a number in brackets / Google Sheet/Excel

I currently have cell that has the following output from my Google Sheet: Share Price vs. Fair ValueBelow Fair Value: AAPL ($126.85) is trading above our estimate of fairvalue ($118.02)Significantly ...
user avatar
0 votes
1 answer
114 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 ...
user avatar
-1 votes
1 answer
51 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. ...
user avatar
0 votes
1 answer
96 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"...
user avatar
  • 15
1 vote
3 answers
37 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 ...
user avatar
  • 117
0 votes
1 answer
21 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
user avatar
0 votes
1 answer
784 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, ...
user avatar
1 vote
1 answer
26 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 ...
user avatar
1 vote
1 answer
136 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 ...
user avatar
0 votes
1 answer
551 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"}...
user avatar
2 votes
1 answer
80 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 ...
user avatar
3 votes
3 answers
112 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 ...
user avatar
  • 145
1 vote
2 answers
48 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 ...
user avatar
  • 153
1 vote
1 answer
145 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, (...
user avatar
  • 145
0 votes
2 answers
4k 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 ...
user avatar
3 votes
1 answer
200 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&...
user avatar
1 vote
1 answer
28 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,...
user avatar
  • 151
2 votes
2 answers
85 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, ...
user avatar
1 vote
1 answer
2k 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 ...
user avatar
  • 145
1 vote
2 answers
68 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 ...
user avatar
  • 13
-1 votes
1 answer
170 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 ...
user avatar
  • 1
0 votes
2 answers
79 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, ...
user avatar
0 votes
1 answer
45 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 ...
user avatar
1 vote
1 answer
378 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 ...
user avatar
  • 121
-1 votes
1 answer
461 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
user avatar
  • 111
1 vote
2 answers
723 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, ...
user avatar
  • 111
1 vote
1 answer
219 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, ...
user avatar
  • 13
0 votes
0 answers
440 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); ...
user avatar
1 vote
1 answer
306 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/...
user avatar
  • 608
0 votes
2 answers
929 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 ...
user avatar
  • 103
0 votes
1 answer
276 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 ...
user avatar
  • 111
1 vote
1 answer
2k 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.
user avatar
1 vote
1 answer
33 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,"******"))
user avatar
0 votes
1 answer
386 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" ...
user avatar
  • 15
1 vote
1 answer
334 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:...
user avatar
  • 15
1 vote
1 answer
1k 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,...
user avatar
  • 25
1 vote
2 answers
120 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 * ...
user avatar
  • 147