Un-concatenate values without Text to Columns

[jimdmart]

Hello everyone!

I'm trying to separate data that was separated by a comma when I was downloading a report from Salesforce. Below is an example of the data I got. I don't want to use Text to Columns because there are about 20 more columns and about ~30K rows worth of values (here I only show 4 each column in order to simplify things). This makes the the process take about 25 minutes to run.

Let me know if there is any other data I can provide

Thank you in advance

[DataNath · Accepted solution]

Hey @jimdmart, totally understand the thinking behind not wanting to use a Text-To-Columns tool for every field you want to split. Fortunately, we can get around this (and as a result make the process dynamic to N number of fields), by first transposing the data so that all values sit in a single column, next to their respective name in another field. Once they're all in the same field, we can split them, assign unique identifiers to stop them becoming re-concatenated and then Cross-Tab back into our preferred format. Here's a breakdown of the steps and the final results:

Record ID:

Bring all 'values' into a single field to split:

Split said column to rows:

Assign a unique identifier to each Record ID - Name pair to keep results split when re-aggregating:

Cross-Tab back into original table form:

Remove temporary Record ID/unique identifier fields (can also re-order etc here if desired):

Workflow shown here is attached for you to look over. Hope this helps you out!