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

[asanjuan]

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

[binuacs]

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

[Qiu · Accepted solution]

@asanjuan 
As @binuacs 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.

 

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