Google-sheets – Google Sheets – How to extract words from a cell using regexextract

formulasgoogle sheetsregexextract

I have a spreadsheet with many rows like this:

Arkonor 16m³Crimson +5%Prime +10%Flawless +15%

Bistot 16m³Triclinic +5%Monoclinic +10%Cubic +15%

Crokite 16m³Sharp +5%Crystalline +10%Pellucid +15%

How do I extract words from these cells? Example, in the first row, I would like to extract words Arkonor, Crimson, Prime and Flawless, one in each cell if possible.

Best Answer

=TRANSPOSE(SPLIT(TRIM(SUBSTITUTE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(
 D1,"\b\w[^A-z]*\b"," "),"\W+"," "),"[0-9]+","")," m "," "))," "))

Related Solutions

Google-sheets – How to duplicate two cells from a column to rows in a particular order

This can be done with a single array formula such as

={A1:A5, A2:A6}

(assuming the dates you've shown are in the range A1:A6).

If you expect more dates to be added, and don't want to specify the last row, then two separate arrayformulas should be used for two columns: ={A1:A} and ={A2:A}.

Google-sheets – Extract nth occurrence of a number from a string

String in A2:

Represents a 90% confidence range based on a sample of 280 runs.
100 runs are done at a time.
Add data to the log (requires JavaScript).

=REGEXEXTRACT(A2,"(\d+) runs ")

If the test string is of the exact format as shown above, the above REGEX should work.

\d+ represents one or more digits.
() inside REGEX represent capture group.

EDIT:

Based on the clarification that test strings can also be "run" instead of "runs",

=REGEXEXTRACT(A2,"(\d+) run(?:s){0,1} ")

=REGEXEXTRACT(A2,"(?m)^(\d+)")

should work.

Ref: https://github.com/google/re2/blob/master/doc/syntax.txt