Parsing Question on Comment Field

[jmuetzel]

I need to pull specific pieces of information from a comment field when available and I'm having trouble wrapping my brain about the best way to go about it. For example, my field might contain: "1 Comments: [Comment#1] 2/16/2017. CSAR Case # 09931449. Amazon chargeback # 9999999999SC is a shortage claim for the following items from invoice # 95317850001: 1/0073523534, 2/0078036771, 2/0078036887, 9/007803843X, 3/1259158632, 18/1259158632. These items were in mixed cartons, mostly partial quantities, in large freight shipments. The shortage credit is on SO # 42668275" I need to pull out 09931449 and put it in a new field called Case #; 95317850001 and put it in a new field called Orig Invoice #; and 42668275 and put it in a new field called SO#. . . . . . Case # Orig Invoice # SO# . . . . . 09931449 95317850001 42668275 Any help would be greatly appreciated.

[estherb47 · Accepted solution]

Hello,

This is a job for Regex! As long as you find the words, "case", "invoice", and "so" in your long text field, the following RegEx Parse tool, with the Parse method, should clean it up for you:

case.*?(\d+).+invoice.*?(\d+).+so.*?(\d+)

This looks for the word "case" and then extracts the digits that follow it. Same goes for Invoice and so. I renamed the new fields in the tool thusly:


Hope it helps!!

[jmuetzel]

This worked like a charm. Thanks so much! You're an angel for getting back to me so fast.