0

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

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

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

  • Thank you! Worked great. For the colors, though, what if the color is two or more words (navy blue, sky blue, etc.). How does one account for that? – Bardo Jul 11 '16 at 4:41
  • and can it be nested in a query? for instance, select all the men's t-shirts through a query: =QUERY(A2:B,"select B where B contains ""Men's T-Shirt""") and use the extract to just pull the three characteristics above? – Bardo Jul 11 '16 at 4:42
  • You mean like a list of just the Men's without seeing the source text? Just a nested formula? – Aurielle Perlmann Jul 11 '16 at 4:49
  • 1
    @Bardo I made an addition - i think this is what your asking - let me know if it's not – Aurielle Perlmann Jul 11 '16 at 5:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.