5
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. An advantage they have over ARRAYFORMULA is that spreadsheets using it ...
- 169
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 ...
- 4,721
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 ...
- 26k
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 ...
- 52.1k
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 ...
- 315
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 ...
- 4,721
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/(\...
- 521
2
votes
Drop part of a cell value after a specific character
You can just wrap it with trim
=Trim (split (B5,"|"))
- 4,721
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
- 4,721
2
votes
Accepted
How to incorporate SUBSTITUTE function into complicated function in Google Sheets
Short answer
=ARRAYFORMULA(
SUBSTITUTE(
IFERROR(
REGEXEXTRACT(
"|"®EXREPLACE(F2:F,"\n","|"),
"^"&REPT("\|[^|]*",
COLUMN(OFFSET(C1,,,1,5))-1)&"\|([^|]*)")
...
- 43.9k
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")
- 3,232
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("&...
- 12.5k
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 ...
- 949
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:
=...
- 26k
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 ...
- 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(...
- 26k
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)
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(...
- 26k
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) ...
- 43.9k
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""\: ""(...
- 26k
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),"...
- 17.3k
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))
- 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)
- 145
1
vote
Split rows dynamically based on row length (COUNTA) and transpose to 2D array
Solved by Lance over on Google forums.
=ARRAYFORMULA(RegexReplace({
Transpose(Split(TextJoin("@";1;IF(RegexMatch(IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(...
- 51
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 ...
- 126
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.
- 111
1
vote
Only top scored, non community-wiki answers of a minimum length are eligible