Google Sheets – How to Extract Info from Text String with Regular Expressions

google sheetsregexextract

I'm new to using regular expressions and I'm trying to use REGEXEXTRACT to pull info from a larger text string into separate cells:

Men's T-Shirt (Amount: 6.00 USD, Qty: 1, Size: M, Color: Black)

I'd like three cells/columns returned (a separate formula for each column), one which returns the quantity ("Qty: 1"), one that returns the size ("Size: M") and one that returns the color ("Color: Black")

Best Answer

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

enter image description here

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 is consistent for all your cells, it should work fine.

ADDITION:

If I understand your second question correctly I think this is what you want - the only other major change, is that you run the array formula so that it's dynamic you do have separate them to 3 formulas - but yes you can nest them. You can also have the shirt type query dynamic - for example in my image i put "Baby's T-shirts" in the cell above the results, just as an example, if you change that cell to Men's it pulls in Men's, so you could hypothetically change that to anything you want:

The three formulas are: 

=ARRAYFORMULA(IF(ISTEXT(QUERY(A3:A,"select A where A contains """&B1&"""")),REGEXEXTRACT(QUERY(A3:A,"select A where A contains """&B1&""""),".*Qty: (\d+),.*Size:"),))

=ARRAYFORMULA(IF(ISTEXT(QUERY(A3:A,"select A where A contains """&B1&"""")),REGEXEXTRACT(QUERY(A3:A,"select A where A contains """&B1&""""),"Size: (\w+),.*Color"),))

=ARRAYFORMULA(IF(ISTEXT(QUERY(A3:A,"select A where A contains """&B1&"""")),REGEXEXTRACT(QUERY(A3:A,"select A where A contains """&B1&""""),"Color: (.*)\)"),))

It looks like this in the end:

enter image description here

Related Topic