I have a data source where a field has text Data that I need to extract into a series of new columns based on certain conditions.
The data in the feilds looks like this:
1000 HR 100 TO
500 TO 1000HR 5YR
12YR/ 500TO/NOTE
as you can see there is not a consistent format.
One thing that is consistent is it is always a Value followed by a unit of measurement.
My idea is to purge all chars except letters or numbers.
I then need to extract the number into a value A column and the corresponding measurement value into Measurement A column and so on for Value B,C and D.
Once this is done i need to do some nested if conditions to transfer all of the values into yet another set of columns for each Measurement.
Im struggling in where to begin.
I can provide more info if required
