I have a long strings in Column A that I would like to split into columns.
I require the string to be split into a new column each time the following occurs: 'dataItem name ='
There could be numerous times that this reoccurs in my string.
If I used text to columns, the delimiter is limited to a single character.
How can I achieve this?
Solved! Go to Solution.
Use a Formula to Replace any instance of 'dataItem name =' with one character, perhaps a pipe |
Then you can use the Text to Columns to split on that delimiter!
Thanks, that's an interesting solution!
My strings can be 1000's of characters and are raw system information using a wide variety of characters. However, ultimately there will be consistency in the characters used so there should be one that isn't used such as the pipe!
Thank you
No worries @gflanagan !
You can do this by adding a delimiter in your string before you use text to column. Do the following:
attach a formula tool to your data, and write the following formula
Replace([Field1], "dataItem name ='", "|dataItem name ='|")
What will happen is now you have a delimiter | that separate your data at eveytime "dataItem name ='" is present in your data ([Field1] in above example).
Now add your text to column and use | as delimiter. Refer snip below.