Segregating mixed data from one columns to various columns

[GDsilva1]

Hi Alteryx Community,  

I'm looking to create three new columns with data from an existing " Comments" column. In this example i have one column " Comments" as described below, which contains customer and vendor ID and other data. I need to know how to segregate this column to create three sperate columns with headers as follows:

1) Vendor ID ( the number that follows the vendor/vend should be in this column)

2) Customer ID ( The number that follows CUST/CUSTOMER should fall into this column)

3) Other data ( all other data like 2021 Launch etc. should fall into this column)

Comments 

VENDOR 90001,CUST-91700
VENDOR 96100 ,CUST-98000
2021 LAUNCH
2021 LAUNCH
VENDOR ID-96000
VEND-96800, CUSTOMER-91600
VEND-96810, CUST-91601

Could someone in the group please help with what formula I need to use or how I can go about achieving the end result.

Your help is much appreciated.

Thank you,

G.

[hroderick-thr]

After inputting the data as a single column csv format

Add a record id

Run data through two paths paths, one for Vendor and another for Customer

In one path parse a vendor column out of the input column

In another path parse a customer column out of the input column

Join the two paths on record id resulting in 4 columns:

record id, raw input, vendor, customer

add a 5th column based on raw input but replacing vendor with '' and customer with ''.

This 5th column becomes your comments column.

[apathetichell]

someone better than me at regex can fix this formula but basically on regex parse if you use:

.*[\s-].*(\d{5}).*cust.*[-](\d*)|id-(\d*)|(\d.*)

You'll get

1) vendor ids - except the one labeled vendor ID-

2) customer ids

3) the missing vendor ID

4) everything else.

You can then use a formula to move the missing vendor id over...

This works on your test set - don't know about your full data. You may want to replace/clean it or wait for the regex superheros to chime in with something better that picks up everything at once...

[GDsilva1]

Thank you for your response. 

[GDsilva1]

Thank you for your response , it worked on some data combinations not all. Will keep trying thanks for your help.