Regex? Extract non-uniform string text

I welcome your expertise. I have data formatted in a long text field in Excel and need to extract the number field (such as 590288-01 in the first example below.) My challenge is that this number is not necessarily in the same place within each record. The number typically, but not always has an extension of 01, 02, etc.

Sample records:

Originator To Beneficiary Info: /ROC/PLAN15987 OTabcW 590288-01 AMIGOIN FUNHOUSE, 23

Originator To Beneficiary Info: REFERENCE 1260422-01 BOB CASS IDY AND ASSOCIATES

Originator To Beneficiary Info: THE DIRECTED ACCOUNT PLAN PLAN 385222-02

Thank you!

Originator To Beneficiary Info: STATE OF BLISS #98988-01

Originator To Beneficiary Info: Applied Pumpkin Plan 45486901

Developer

Developer Tools

Accepted answers

Claje

Hi,

Based on your examples, I identified the following defining characteristics. If these are inaccurate, the below REGEX will not work.

the target number field is one of the following

a set of digits up to 6 in length followed by a dash ("-") and two digits

a set of digits of length 7 or 8 with no dashes or spaces.

((?:\d{0,6}-\d{2})|(?:\d{7,8}))

This regex looks for either a set of 0 to 6 digits, followed by a dash and 2 digits, OR a set of 7 or 8 digits.

Hope this helps!

MarqueeCrew

@Empower49,

Along with @Claje, I used a regex expression to create your desired output.

REGEX_Replace([Field1], ".*([0-9]{5,7}\-{0,1}[0-9]{2}).*", '$1')

\d is a shortcut for the set of characters between 0 and 9. I look for a group (enclosed within parenthesis) that comes after any characters where the group contains:

5-7 characters of "digits"
followed by 0 or 1 dash symbols
followed by 2 digits

This solves for your desired results too.

Cheers,

Mark

MarqueeCrew

@Empower49,

I think that this is what you need:

REGEX_Replace([Field1], ".*Originator To Beneficiary Info:.*?([0-9]{5,8}\-{0,1}[0-9]{2}).*", '$1')

you can modify my prior workflow and use this formula.

Cheers,

Mark

All comments

Claje

Hi,

Based on your examples, I identified the following defining characteristics. If these are inaccurate, the below REGEX will not work.

the target number field is one of the following

a set of digits up to 6 in length followed by a dash ("-") and two digits

a set of digits of length 7 or 8 with no dashes or spaces.

((?:\d{0,6}-\d{2})|(?:\d{7,8}))

This regex looks for either a set of 0 to 6 digits, followed by a dash and 2 digits, OR a set of 7 or 8 digits.

Hope this helps!

MarqueeCrew

@Empower49,

Along with @Claje, I used a regex expression to create your desired output.

REGEX_Replace([Field1], ".*([0-9]{5,7}\-{0,1}[0-9]{2}).*", '$1')

\d is a shortcut for the set of characters between 0 and 9. I look for a group (enclosed within parenthesis) that comes after any characters where the group contains:

5-7 characters of "digits"
followed by 0 or 1 dash symbols
followed by 2 digits

This solves for your desired results too.

Cheers,

Mark

Empower49

Thank you! This is fabulous. This works for many of the records, but some of the records have other numbers in them in that are not part of the "Originator To Beneficiary Info:" string. These values are being returned in my output when I only need the value of 590288-01 below. For example, I have the following in one Excel field:

Originator To Beneficiary Info: /ROC/PLAN15987 OTabcW 590288-01 AMIGOIN FUNHOUSE, 23

Reference #: P482973

ID:893723

Thanks SO much!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703