How to bring portion of a field into header while leaving remaining characters in field.

[NRC]

I'm using version 2021.3.6.01129

I need to find a way to pull all characters prior to the semi colon into the header of that specific column while leaving the remaining characters in the current field.  The order and quantity of columns will be inconsistent from file to file and so I'd like the workflow to be dynamic based on the assumption that characters prior to the ":" will become the column header while remaining characters will become the field value. 

Example being the first row in the screen shot:

Column 7 shows  "Sales $: $ 3.540.29"

Column 9 shows: "FIELDREC: 20492"

Column 11 shows:  "GROWID: 25436"

Desired outcome:

Column 7 be renamed to "Sales $" while leaving "$3,540.29" in the current field

Column 9 be renamed to "FIELDREC" while leaving "20492 "in the current field 

Column 11 be renamed to "GROWID" while leaving "25436" in the current field

The dynamic portion of this needs to consider that the elements (desired column headers) will vary in order, quantity and length from the input data.  Likewise, the remaining characters will range from alpha, numeric, special, etc.   Please see the attached screen shots for further example.  "Example Data Current" is how the data sits currently and "Desired Outcome" is how I'd like to have it rearranged.  

[Kakuffo]

HI @NRC I would use transpose tool to select all fields with headers and values into data columns, Then use a text to columns tool to split the header data from the values by specifiying the : as the delimer this will elave you with name = field header datat and Value the values that sit underneath, a cross tab tool will then allow you to position the name field as column headers,,,,, example incoming.....

[CarliE · Accepted solution]

Hi

Here is a solution that is dynamic:

If this helped to resolve your issue, please make sure to mark it as a solution!

[Kakuffo]

example attached