community
cancel
Showing results for 
Search instead for 
Did you mean: 

community Lounge

Get to know your fellow analytics buffs and discuss a wide range of topics!
SOLVED

RegEx Problem

Meteor

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.

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.

Meteor

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. 

Alteryx
Alteryx

@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.