RegEx Parsing

[Cwynot]

Hello,

I'm working on a workflow and I've got a string of text that I'm trying to parse out into cleaner data. However, I'm using the RegEx tool and it's not working. I've also tried using the formula substring or findstring, however, was unsuccessful. Below is an example of my text as it is now and I need it split out by PO #, Item # and Supplier Name. However, when it gets to a string that doesn't have a PO # I want it to return blank. Not sure if there is a way to do a search for a specific word and then return all the cells to the next character like in this situation is the comma. It also cannot count characters due to the number in characters in each section could be different.

Current Data:

PO #: 123456, PO Receipt , ITEM: 12345678-123 , TRX ID: 123456789 , Supplier: Cwynot LLC

INV VCHR #: 1234567 , INV TRX #: 123-0 , INV DT: 14-JUL-20 , STD INV , Cwynot LLC

How I want it:

New Column PO #: 123456

New Column Item #: 12345678-123

New Column TRX ID: 123456789

New Column Supplier Name: Cwynot LLC

[atcodedog05 · Accepted solution]

Hi @Cwynot 

Here is a workflow for the task.

Use below pattern in Regex tool parse mode.

PO #: (.*), PO Receipt , ITEM: (.*) , TRX ID: (.*) , Supplier: (.*)

Config:

Workflow:

Output:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[echuong1]

This can be achieved with text to columns by splitting to both rows and values. You can also achieve this with a regex as well.

Both methods will work with multiple rows as well.

[Cwynot]

Thank you so much! Worked like a charm, guess I never realized I could just use my PO # in the actual expression in that way. 

[atcodedog05]

Happy to help 🙂 @Cwynot 

Cheers and Happy Analyzing 😀