Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

General Discussions

Discuss any topics that are not product-specific here.
SOLVED

RegEx Problem

bdelarosa
8 - Asteroid

Hi ladies and gentlemen. I am a beginner at regex, can someone help me with this problem? 

 

for example if in the column it says

 

Natural Light 16oz 8pk in column All I want in column B is 16oz for each row. Can someone help and post the formula you used in regex so I can study? Thanks.

3 REPLIES 3
Lwt08
6 - Meteoroid

Hi @bdelarosa,

 

I would recommend using the regex of "[0-9]+oz" I am using the + which means one or more instead of {2} in case you come across 123oz or 5oz. Hopefully, this extracts your desired string.

bdelarosa
8 - Asteroid

perfect thanks.

 

we do have products that have 23.5oz or 11.2oz, but thru your logic I was able to figure out "[0-9]+oz | [0-9]+[.][0-9]+oz" fixed everything, thanks for taking the time to help out. 

KevinP
Alteryx Alumni (Retired)

@bdelarosa I would actually recommend a slightly more flexible expression for this. While the express '[0-9]+oz' @Lwt08 presented does get the job done for most strings it doesn't take into consideration variances in capitalization or fractional ounces. As such it would return '16oz' per the provided example, but will not return 23.5oz or 16Oz as per some examples from your sample data. As such I would recommend a more robust expression that takes these additional patterns into account. The following expression more accurately describes the possible patterns based on your sample data.

 

^.*\s([0-9\.]+[Oo][Zz]).*$

 

This expression can be used directly in the Regex tool in Designer with the parse option to achieve your desired result. The important part is the logical group '[0-9\.]+[Oo][Zz]' contained within the parenthesis. This expression looks to match/parse one or more digits with or without a decimal place after a white space character and immediately followed by the letters 'oz' in any form of upper or lower case. The parts outside of the parenthesis just generically match the rest of the string from the beginning to the end.  

Labels