Regex Parse - varying character lengths & ending before specified word

[jbfries]

I have a listing of data that I need to parse from. Unfortunately the names or IDs I want have varying lengths. My goal is to have

1. the type of activity, i.e. Edit Vendor or Approve Vendor in one column.

2. The digits that following the Vendor ID in a column and

3. All characters that follow 'Vendor Name' and occur before the 'Primary Payment type' string begins, in one column. Example of strings below:

Ex 1: Edit Vendor [VendorID=2663641752479 VendorName=JOHN FAKE DOE PrimaryPaymentType=ACH

Ex 2: Approve Vendor [VendorID =250092118591 VendorName =FAKE COMPANY Primary PaymentType =ACH

I was able to solve goal #2 with the following regex: (.*?=)(\d*)(.*) output being: 2663641752479

and then used a subsequent regex parse to extract the string to fulfill goal #1 , (\w*\s\w*). output being: Edit Vendor

I am struggling to achieve #3 as the Vendor Names vary in length and I have not figured out how to end the parse upon Regex seeing the word Primary. Desired output would be JOHN FAKE DOE

Any thoughts on improvements to my technique and/or how to accomplish #3 would be much appreciated! I am new to Regex parsing. 

Thank you

[afv2688 · Accepted solution]

Hello @jbfries ,

Would this help you?

Regards

[jbfries]

Hi, 

Works great! With exception to the below that I have realized regarding the Payment type formula:

The current formula for Payment Type: Trim(REGEX_Replace([Description], '.*(\w{3})', '$1')), works when the string data resembles Ex 1 below (it includes the ] but I could replace that after). However, when string data resembles Ex 2 is results in the output, ode=22.

I tried playing with this formula, but haven't been able to extract the payment type.

Ex 1: Approve Vendor [VendorID =2663641752479 VendorName =JOHN FAKE DOE Primary PaymentType =ACH]

Ex 2: Edit Vendor [VendorID=2663641752479 VendorName=JOHN FAKE DOE PrimaryPaymentType=ACH VendorStatus=A ACHReceiverName=JOHN FAKE DOE ACHIndividualId= ACHReceiverABA=063100277 ACHReceiverAccountNumber=898013314652 ACHTransactionCode=22 ACHType=PPD A...

Thanks!

[MarqueeCrew · Accepted solution]

@jbfries ,

If you use a RegEx tool and set it to PARSE, you can parse Field1 with:

(^.*?)\s\[.*VendorID\s*=(\d+)\D.*VendorName\s*=\s*(.*)\sPrimary\s*Payment.*

Cheers,

Mark

[jbfries]

Thank you as well 

How did you all become so familiar with Regex and applying it to strings? 

I've watched a few of the Alteryx Interactive lessons, but they seem very base level and in real life application these data strings can become much more complex

[MarqueeCrew]

Google, YouTube, RegEx101.com

Trial and error works.  RegEx101.com is a good place to test (but Alteryx works too).  That website explains what's happening in the expression and gives you lots of options to try/see.

Cheers,

Mark (did you subscribe and check my regex videos?)