RegEx Problem

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.

RegEx Problem.xlsx

Help

Accepted answers

Lwt08

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.

All comments

Lwt08

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

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

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703