Normalizing bad table....

[danloz]

Hi,

Sorry about the bad subject line, I don't know how to better title it.  I actually have a solution for this problem but I'm relatively certain there's a better or more efficient way that I'm not familiar with so I'm asking how the pros handle this sort of thing.  

the setup:  

I have table that goes like so:  

Key    |    Val

1             100

2             200

3,4          300

5;6          400

7             500

Objective would be to return:  

Key|Val

1  100

2   200

3  300

4  300

5  400

6  400

7  500

The problem:  

A)  I don't really know How many keys a record could have.  I'm observing 2, but ideally I would make this dynamic.  

B)  I don't really know how many different delimiters I might see although I expect this to be a more limited concern than A.  

My current solution:  

A)  Filters for observed muti key situations using "contains(a_char,my_string)"

B)  Uses TTC to split out the key

C)  Overwrites the original with the TTC results.  

This works, but it's noisy and it's got some bounds where it will works but anything out of that tolerance will fail. 

[apathetichell]

Does text to columns ;, delimited split to rows on your key field work?

I'll post that and regex token version in a second

[apathetichell · Accepted solution]

regex version ignores the specific delimiter and just breaks down any set of numbers into a specific row for each number.

[danloz]

That is awesome and I am embarrassed that I did not "see" that solution.  Thank you.  

[apathetichell]

trust me -  we've all been there!