Extract measurement Unit of Measure (UOM) to next column over to the right

I have data stored in a format where the column header is rather generic (Attribute Name 1, Attribute Name 2) - I have about 50 attribute fields like this. I'm trying to programattically (meaning not do it one attribute at a time) either add or extract the Unit of measure from those attributes that are some sort of dimension.

For example in this:

For those attribute values that are a number (or text stored as a number), note not all the attributes in the column are the same, I'd like to put 'in' for inches in the Attribute_UOM_XX column to the right of the value.

And for those values where they have the inch marks like 2", I'd like to remove the inch marks from the value and put 'in' in the Attribute_UOM_XX column to the right of the value. Note that here elsewhere in the data the inch marks could be CM, M, FT, ft, ' etc. It could be 2 CM, or 2cm.

Any help on how this might be accomplished without specifically writing a calculated field via regex 50 times would be appreciated.

Can you post a sample data file? It would be easier to answer :slight_smile:

I would unpivot the table first and then re-pivot it in a way that all attributes are in one column, and all units of measure are in an adjacent column. Then it would be much easier to make the necessary changes.

Sorry for the delayed reply as I was on leave - here's a sample file with some data with the inch marks, some without and in random spots within the columns.
Sample.xlsx (16.8 KB)

Here you go.

Notes:

  • Some tables and actions have useful annotations
  • The algorithm assumes that EXTERNAL_SYSTEM_ITEM_ID is unique. Otherwise, rows in the input dataset must be enumerated.

uom.morph (23.5 KB)