Separate String Field into Two Columns

Question

Hello,

I would like to separate this Vendor field into two columns: Vendor ID and Vendor Name

The ID and name is separated by " : " so I'm trying to grab only the digits on the left side of the colon into a new Vendor ID column. And grab only the string on the right side of the colon into a new Vendor Name column.

For this example, the output I'm looking for would be:

Vendor IDVendor Name90000075EAGLE PRESS INC

Thank you in advance for your help!

Qiu · Accepted Answer

@jxkcho 
maybe something like this?

atcodedog05 · Accepted Answer

Hi @jxkcho

Here is how you can do it. You can use below regex in regex tool parse mode.

Workflow:

1. Here i am using regex (\d+) : (.+). Where (\d+) captures vendor ID(number) and (.+) captures vendor name after :

2. And you set the rename for the new columns in the output columns section.

Hope this helps 🙂