8

A kind of loop-like behavior can be achieved using array formulas. You can enter following formula: =SUM(ARRAYFORMULA(VALUE(REGEXEXTRACT(B2:B4;"\d+")))) in a cell to have there computed the value you need in range you want.


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


3

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


3

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


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

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

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

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


2

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


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

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

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 inclued as the first argument of SUBSTITUTE and this was this was included as ...


1

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


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

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

Well, I got it to work changing my expression to: ^[\w\s\\À-ÿ-’']+ so double escaping seems to be required in this case


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