Segregating mixed data from one columns to various columns

Question

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.

GDsilva1 · Answer

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

GDsilva1 · Answer

Thank you for your response.

apathetichell · Answer

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...