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 |
Solved! Go to Solution.
@asanjuan sorry I am not following the requirement. if possible can you provide an input file with expected output
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.
@asanjuan
Thank you for the feedback.
I believe you can solve 😀 it and sorry I was only able to provide a start point.