How do I rename columns using the first half of the column values?

[helenjin1]

Hi, I have multiple columns that are written in Column_Title:### format and I would like to rename the column titles using everything before ":".

So far I've tried to use dynamic rename but I don't think that would work. I also could use text to columns with ":" as my delimiter but I don't want to create 22 text to column icons.

I think using RegEx might help me but I don't know how to use it. Can someone point me in the right direction?

[john_miller9 · Accepted solution]

You would want to first sample 1 row of data, then transform it so that it's a tall dataset, and then parse that field.  You can cross-tab that back as headers for your data

[CharlieS]

@john_miller9 has the right idea with that transformation and parse! I might suggest adding a RecordID to so all records are taken care of in this process.