Skip to main content
Share Your Experience: Take the 2024 Developer Survey
6 votes

Is it possible to use a loop in Google Spreadsheets?

Updated 2023 Answer Now you can use functions like MAP and REDUCE, which combined with LAMBDA allows for very powerful formulas. ARRAYFORMULA cannot be exported, so my method has the advantage of ...
Daniel Reina's user avatar
5 votes
Accepted

Pull info from a larger text string into separate cells with regular expressions

IF you have the text for example in A1 you only need one formula to extract all 3 portions: =REGEXEXTRACT(A2,".*Qty: (\d+),.*Size: (\w+),.*Color: (\w+)") To explain a little: Anything you enclose ...
Aurielle Perlmann's user avatar
4 votes
Accepted

How to apply a relative reference in conditional formatting formula?

Try this: =INDIRECT(ADDRESS(ROW(),COLUMN(),4))/REGEXEXTRACT(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4)),"\((.*)\)")*1>1 INDIRECT ADDRESS 1 ROW COLUMN 1 The third parameter in ADDRESS() is ...
ale's user avatar
  • 52.7k
4 votes
Accepted

Drop part of a cell value after a specific character

=REGEXEXTRACT(B5, "[^\s|]+") \s a space \s| a space followed by | character ^ the first ^\s| the first match of a space followed by | character [] a group [^\s|] a group found before the first match ...
user0's user avatar
  • 26.7k
4 votes

Drop part of a cell value after a specific character

Split formula may fit your needs: =split(B5, "|") next step: use index to return any of splitted words: =TRIM(INDEX(split(B5, "|"),1)) change 1 to your number, this is the index of a word to ...
Max Makhrov's user avatar
4 votes
Accepted

How to categorize data in Google Spreadsheet using regular expressions

SO here is what I did - I added a sheet to your doc called SO Test - Aurielle. Then I made a unique list of the possible categories in column B using: =UNIQUE(Words!B:B) In Column A, I did a JOIN ...
Aurielle Perlmann's user avatar
3 votes
Accepted

How to extract multiple paths from a single string of URLs in Google Sheets?

You have the basic concept right just piece it together step by step. This does the job: =REGEXREPLACE(JOIN(" ",ARRAYFORMULA(IFERROR( REGEXEXTRACT(SPLIT(A1," ",TRUE,TRUE), "https://example.com/(\...
Cornelius Roemer's user avatar
2 votes

Drop part of a cell value after a specific character

You can just wrap it with trim =Trim (split (B5,"|"))
Aurielle Perlmann's user avatar
2 votes

How to modify REGEXEXTRACT to import total hours played on Steam

You can use this regex: =REGEXEXTRACT(A1,"hours_forever"":""([\d,]+)"",") the \d and , inside the [] brackets means it will only extract either a digit or a comma
Aurielle Perlmann's user avatar
2 votes
Accepted

How to incorporate SUBSTITUTE function into complicated function in Google Sheets

Short answer =ARRAYFORMULA( SUBSTITUTE( IFERROR( REGEXEXTRACT( "|"&REGEXREPLACE(F2:F,"\n","|"), "^"&REPT("\|[^|]*", COLUMN(OFFSET(C1,,,1,5))-1)&"\|([^|]*)") ...
Rubén - Volunteer Moderator -'s user avatar
2 votes
Accepted

Google Sheets: REGEXEXTRACT Formula to remove characters not inside [brackets] but leave , commas?

Try =REGEXREPLACE(A2,"\w+\s*\d*\[?(\b\S+\b)?\]?(,?)(\s*|$)","$1$2")
TheMaster's user avatar
  • 3,342
2 votes

Extracting text from a capture group while using an arrayformula to consider multiple criteria

You would need to alter your "regular expression" in the sample regex from: ((?:[[:alpha:]]+\s?)+) to: (?:[[:alpha:]]+\s?)+ Following that, your formula will work just fine. =textjoin("&...
marikamitsos's user avatar
  • 12.9k
2 votes
Accepted

Regexextract to get a text in the middle of string

Using a capturing group, we can match a pattern then choose which part of the pattern we want to return. =ArrayFormula(REGEXEXTRACT(A2:A4,"^\d\s+(\w\d+\s+-\s+[A-z 0-9]+)")) Notes: I'm ...
David Tan's user avatar
  • 1,005
1 vote
Accepted

Google Sheets - REGEX Challenge

Here is a possible solution. First, I am assuming that your data is all in column a of the sheet and each two lines of text seperated by a blank line represent one cell. Also I am assuming that the ...
webstermath's user avatar
1 vote
Accepted

Conditional color scale not working with functions

Okay, I actually solved it. The answer is using VALUE() to convert a string to number, so using: =VALUE(REGEXEXTRACT(ProcessedData!A12&".";"\b+[0-9]+\b")) It works perfectly.
jajube's user avatar
  • 111
1 vote
Accepted

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

This should work: =REGEXEXTRACT(REGEXREPLACE(A1,".*_", ""),"[A-Z]+")
Amit Agarwal's user avatar
  • 1,988
1 vote
Accepted

How to count cell with upper leter in array?

=ARRAYFORMULA(IF(LEN(A1:A&B1:B&C1:C&D1:D&E1:E), MMULT(IFERROR(LEN(IFERROR(REGEXEXTRACT(A1:E, "[A-Z]+")))/ LEN(IFERROR(REGEXEXTRACT(A1:E, "[A-Z]+"))), 0), TRANSPOSE(...
user0's user avatar
  • 26.7k
1 vote
Accepted

Google Sheets Conditional Formatting

=VALUE(REGEXEXTRACT(A4,"\d+"))=WEEKNUM(TODAY(),2)
user0's user avatar
  • 26.7k
1 vote
Accepted

Remove all text before (and excluding) match

=REGEXEXTRACT(A1, "http.*") ______________________________________________________ =ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(REGEXEXTRACT(D2:D, "(\d+.\d+,.*)|(http.*)|\((.*)\)"))),,999^...
user0's user avatar
  • 26.7k
1 vote
Accepted

Format cell, If word from list in cell

for all numbers: =IF(ISNUMBER(A1), 1) for specific digits (2 and 3): =REGEXEXTRACT(TO_TEXT(A1), "2|3") for exact words: =REGEXEXTRACT(A1, "yes|nah|yah") for partial (containing) matches: =...
user0's user avatar
  • 26.7k
1 vote

How can I create a recursive formula in Google Sheets?

I solved it. Here is the final formula I used: =IF(ISBLANK(C19), B19, CONCATENATE(REGEXEXTRACT(INDEX($A:$A, C19, 1), "\s*"), " ", B19)) Here's how it works: first we use INDEX to look up the task ...
Rafael's user avatar
  • 21
1 vote
Accepted

Total Wages Calculations

=ARRAYFORMULA(IF(LEN(A2:A), (REGEXEXTRACT(TO_TEXT(F2:F), "\d+")+ (REGEXEXTRACT(TO_TEXT(F2:F), ":(\d+)"))/60+IFERROR (REGEXEXTRACT(TO_TEXT(F2:F), "\d+:\d+:(\d+)")/3600))*H2:H, )) =ARRAYFORMULA(IF(...
user0's user avatar
  • 26.7k
1 vote
Accepted

Google Sheets - Convert entire column of prices by 50%

Just put the below formula in cell B2 and copy it to all other cells. =if(A2="", "", iferror(left(A2, find("]", A2)), "") & round(right(A2, len(A2) - iferror(find("]", A2), 0)), 0)/2)
Md. Jahurul Islam's user avatar
1 vote

Convert Number and Text to Number

to convert text to numbers use this: =ARRAYFORMULA(IFERROR( VALUE(SUBSTITUTE(REGEXEXTRACT(TO_TEXT(A1:A), "\d+,\d+|\d+.\d+|\d+"), ",", ".")), )) for non-US syntax try: =ARRAYFORMULA(IFERROR( VALUE(...
user0's user avatar
  • 26.7k
1 vote
Accepted

Parsing a non-nested JSON string in Google Sheets using formulas only

Assuming that the value of rev2_1 always will have 5 characters use =mid(A1,find("rev2_1",A1)+10,5) Explanation find finds the position of the first character of rev2_1 mid gets a text (substring) ...
Rubén - Volunteer Moderator -'s user avatar
1 vote

Parsing a non-nested JSON string in Google Sheets using formulas only

short formulas: =REGEXEXTRACT(A1;"_1""\: ""(.{5})") =REGEXEXTRACT(A1;"_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(.{5})") =REGEXEXTRACT(A1;"rev2_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(...
user0's user avatar
  • 26.7k
1 vote

Parsing a non-nested JSON string in Google Sheets using formulas only

There might be a shorter way, such as with string manipulation (e.g., MID) or regular expressions (REGEXEXTRACT), but this works: =1*trim(substitute(substitute(substitute(index(SPLIT(A1,":"),3),"...
pnuts's user avatar
  • 17.6k
1 vote

How to SUMIF depending on another column's value and REGEXEXTRACT

Just to let people know, i've solved this now and i was looking at the wrong function, i needed to use ArrayFormula =ArrayFormula(SUM(0+iferror(regexextract(B2:B, "[0-9]*\.[0-9]+[0-9]+")) * C2:C))
Jamie B's user avatar
  • 21
1 vote

Vlookup using Regex Extract

This was solved using the following at this thread =image(iferror(vlookup(A2&"*",Sheet2!A:B,2,0),vlookup("*"&A2&"*",Sheet2!A:B,2,0)),1)
user2240778's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible