Extracting UOM Conversion rate from Packing String

[KSassone]

Subject: Inventory Control, Materials Management, Packing String

We manage the Item Master for our ERP system and have a Packing String for each item that contains the various UOM and their conversion rates.

e.g. (1_RL)(6_BX)(60_CS)

The Conversion Rate and UOM are separated by "_" and each one is encased in ().

I need to be able to locate the UOM in the Packing String, and return the conversion rate. I then use the conversion rate in a calculation later in the workflow.

e.g. if CS is contained in my pack string, I want the value of 60 to be returned. If BX is contained in the pack string, I want the value of 6 returned.

This is the script I used, to no avail:

IF CONTAINS ([PACKING STRING], "CS") THEN
REGEX_Replace([PACKING STRING], [SOURCE UOM], PadLeft([PACKING STRING], 2, "_"))
ELSE ""
ENDIF

"CS" is just an example, the actual field will be [SOURCE UOM] that contains a UOM.

Any direction, very much appreciated !

[BS_THE_ANALYST]

@KSassone is this a good starting step? Split the brackets into columns:

 Then use a few text to columns and a data cleanse to remove punctuation:

 

 

Perhaps a good starting point. 

All the best,

BS

[BS_THE_ANALYST]

@KSassone if you want a 1 tool solution:

 

\(([^_]+)_([^_]+)\)\(([^_]+)_([^_]+)\)\(([^_]+)_([^_]+)\)

My advice with understanding RegEx: copy and paste it into chatbotGPT. Ask it to explain it to you like simply or even like a 5 year old. Works for me. 

All the best,

BS