# Tag Info

5

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 in it's own capture group or set of parentheses, automatically gets pushed to the next cell. For each type, as long as the remaining data you see in the formula ...

4

You could split the cell's value into individual cells. Given that cell A1 contains the number 2113, put the following in cell B1: =SPLIT(REGEXREPLACE("" & A1, "(\d)", "\$1,"), ",") This should result in cells B1:E1 containing each respective digit. What the formula does is three things: The expression "" & A1 converts the input number 2113 to a ...

4

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 return

3

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 absolute_relative_mode [ OPTIONAL - 1 by default ] - An indicator of whether the reference is row/column absolute. 1 is row and column absolute (e.g. \$A\$1), 2 is row ...

2

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 using the regex AND operator which is | and used the formula: =IF(ISTEXT(B2),JOIN("|",FILTER(Words!A:A,Words!B:B=B2)),) Basically the filter restricts it to ...

2

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

2

Short answer =ARRAYFORMULA( SUBSTITUTE( IFERROR( REGEXEXTRACT( "|"&REGEXREPLACE(F2:F,"\n","|"), "^"&REPT("\|[^|]*", COLUMN(OFFSET(C1,,,1,5))-1)&"\|([^|]*)") ), "@","") ) Explanation The argument of the ARRAYFORMULA function, was included as the first argument of SUBSTITUTE and this was this was included as ...

2

=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 of a space followed by | character + more than one character if possible [^\s|]+ extract a group of characters before the first occurrence of a space followed ...

2

Try =REGEXREPLACE(A2,"\w+\s*\d*\[?(\b\S+\b)?\]?(,?)(\s*|\$)","\$1\$2")

2

You can just wrap it with trim =Trim (split (B5,"|"))

2

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/(\S+)"),""))),"\s+"," ") You split the string at whitespaces, this is safe because URLs don't contain white space so you're definitely not breaking them up. This ...

1

=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(LEN(A2:A), H2:H*1.5, )) =ARRAYFORMULA(IF(LEN(A2:A), (REGEXEXTRACT(TO_TEXT(G2:G), "\d+")+IFERROR( (REGEXEXTRACT(TO_TEXT(G2:G), ":(\d+)"))/60)+IFERROR (...

1

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) of certain length (5) from a starting position, in this case the result of find plus 10 characters.

1

short formulas: =REGEXEXTRACT(A1;"_1""\: ""(.{5})") =REGEXEXTRACT(A1;"_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(.{5})") =REGEXEXTRACT(A1;"rev2_1""\: (.{7})") =REGEXEXTRACT(A1;"rev2_1""\: ""(.+)\"", ""rev2_2") =REGEXEXTRACT(A1;"rev2_1""\: (.+)\, ""rev2_2") =REGEXEXTRACT(A1;"_1""\: ""(.+)\"", ""rev2_2") =MID(A1;32;5) this will search for what is ...

1

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),"rev2_2",""),CHAR(34),""),",",""))

1

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))

1

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)

1

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(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);"^##1"); IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2)))...

1

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 pattern in column a repeats exactly after 5 cells. Paste the following formula down column b: =trim(REGEXEXTRACT(A1,"(?:\s)[^\n]*")) Paste the following ...

1

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.

1

You can try =LEFT(B5,SEARCH("|",B5)-1).

1

I need a formula that will show the value of B5, but only the characters before the first "|". =TRIM(LEFT(B5,SEARCH("|",B5)-1) The result is title

1

This should work: =REGEXEXTRACT(REGEXREPLACE(A1,".*_", ""),"[A-Z]+")

1

The other alternative is to combine split, join, repeat with regex extract like this: =join(" ",REGEXEXTRACT(A1,rept(".*(#\w+\S?\w+)",counta(split(A1,"#"))-1))) it basically split out the text by # and then repeats the generic regex to capture it that number of times (-1 for the additional cell it puts out when you use split) - Also it is able to include ...

1

You need to do this. Formula new Google Sheet =REGEXEXTRACT(IMPORTRANGE("URL_IMPORT_SHEET", "A2"), "(\w+\.\w+)\$") old Google Spreadsheet =REGEXEXTRACT(IMPORTRANGE("KEY_IMPORT_SHEET", "A2"), "(\w+\.\w+)\$") For a range you need to do this. =ARRAYFORMULA(REGEXEXTRACT(IMPORTRANGE("URL_OR_KEY", "A2:A14"), "(\w+\.\w+)\$")) References URL or KEY: https://...

1

=REGEXEXTRACT(TO_TEXT(A1); REPT("(.)"; LEN(A1))) =ARRAYFORMULA(REGEXEXTRACT(TO_TEXT(A1:A), REPT("(.)", LEN(A1:A)))) =ARRAYFORMULA(TRANSPOSE(QUERY(TRANSPOSE(REGEXEXTRACT( TO_TEXT(A1:A), REPT("(.)", LEN(A1:A)))), , 999^99)))

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