Ok this one is a bit tricky for me so appreciate any help in advanced. I am working with a data set that unfortunately isn't very user friendly. It essentially has multiple lines in each cell (though not all). I am trying to split them but only when there are multiple lines. Additionally, I want to text to columns so that the number and state are in seperate columns. I realize this is multiple tools / steps but have tried so many different ways and couldn't get it to work I thought I would provide the starting point and desired result.
Raw Data
Desired Result
Solved! Go to Solution.
The most straightforward way would be to use the Text to Columns tool number of times in a sequence.
First, you'd want to split your data into rows rather than columns on an embedded new line characters which are present across all three of those columns - you'll use \n to identify this delimiter.
Once you have all the rows in your data, you will use the same tool in the Split to columns configuration to split each of the on the hyphen.
Here is something that should work for you, even as you add more columns to your data. It's dynamic because it parses into rows, then filters on what are numbers and what aren't, rebuilds the table, and joins back into position.
Let me know if this works.
Cheers!
Esther
