Recognize values with multiple delimiters and convert to values with standard UOMs

Hello, I'm working to convert different versions of units of measures (UOM) from various customer files. I see a lot of garbage that need to be cleansed and converted to Each (EA), Pack (PK) and/or Case (CA). Text to Column tool looks inadequate but I'm not familiar with other parsing tool or how it would need to be written if using a formula. Also, whatever tool is used will need to be smart to anticipate unforeseen characters, delimiters and the occasional misspellings but still convert them to EA, PK and CA. Appreciate the help!

Sample scenarios below:

Convert Old UOM	Case per Pack	To New UOM	Need to convert UOM to calculate deviation later in the workflow
CS	12/CS	CA	CS = CA
Eaches	1 EA	EA	Eaches = EA
PK	1.3M/PK	CA	How do I deal with the M (or T), million or thousand and other roman numerals, etc.?
CS PK	12/CS, 24/PK	PK	What do I do with 2 measures with multiple delimiters including space and invisible characters?
CA	1.9T/CS/PK		Combination Roman numerals and multiple delimiters?
EA	1 E A	case	space delimiter before, after and in between values

Parse

Accepted answers

Qiu

@asanjuan
As @binu_acs mentioned, we may be a more detailed data to get a closer look.

But I feel it might be a Find and Replace issue here.
We can prepare a matching table for the UoM to be replaced which is easy to maintain also.

0601-asanjuan.yxmd

All comments

binu_acs

@asanjuan sorry I am not following the requirement. if possible can you provide an input file with expected output

Qiu

@asanjuan
As @binu_acs mentioned, we may be a more detailed data to get a closer look.

But I feel it might be a Find and Replace issue here.
We can prepare a matching table for the UoM to be replaced which is easy to maintain also.

0601-asanjuan.yxmd

asanjuan

Thanks @binuacs, thanks @Qui for your responses. Sorry, for my late reply. @Qui, your solution gave me part of what I needed. I was trying to avoid creating a reference table, but to your point, I think it's easy enough to maintain in this case.

Qiu

@asanjuan
Thank you for the feedback.
I believe you can solve 😀 it and sorry I was only able to provide a start point.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703