I’m sure, like most, you’ve had to split a column that has multiple delimiters. Assume our delimiters are |. On occasion I have to split a column on the first three pipes in a column that has 5,10, or more columns.
I don’t think I have come across that situation before. Does it come up often? I guess you can Split Col into N columns and then remove the last N - x columns with Remove Cols. It isn’t terribly elegant though.
People often send me data for analysis that are space delimited. The data can be formatted as such, system, subsystem, comments (can be sentences).
I need to would like to split the data based on the the first two spaces system and subsystem and leave the sentence or comments intact.
Attached is a CSV file.
There are 4 columns of interest Item, Brand, Product, and Comment
I would like to split the data into four columns. The first three space delimiters. Note: sometimes the data in the columns vary in the number of alpha-numerics e.g., coff, tea, cases.
Example.csv (1.6 KB)
Thanks for taking a look.
1 Like
Thanks. I will take a look.
You can do it as attached.
split.transform (5.2 KB)
But it is a bit inelegant. I will have a think about if there is a better way. Ideally without significantly increasing the complexity of Split Col.
1 Like
And this is a more elegant approach that makes use of the Max. values option in Split Col.
split2.transform (5.8 KB)
I never realized there was a Max number of columns setting in the split column transform. Thanks
1 Like