A kind of loop-like behavior can be achieved using array formulas. You can enter following formula:
in a cell to have there computed the value you need in range you want.
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 ...
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
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:
In Column A, I did a JOIN using the regex AND operator which is | and used the formula:
Basically the filter restricts it to ...
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 ...
Assuming that the value of rev2_1 always will have 5 characters use
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.
There might be a shorter way, such as with string manipulation (e.g., MID) or regular expressions (REGEXEXTRACT), but this works:
Solved by Lance over on Google forums.
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)))...
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:
Paste the following ...
The argument of the ARRAYFORMULA function, was inclued as the first argument of SUBSTITUTE and this was this was included as ...
The other alternative is to combine split, join, repeat with regex extract like this:
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 ...
You need to do this.
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+)$"))
URL or KEY: https://...