Text to Columns

[gflanagan]

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? 

[alexnajm · Accepted 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!

[gflanagan]

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

[alexnajm]

No worries @gflanagan !

[Gaurav_Dhama_]

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.