Text to Columns

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?

Data Investigation

Accepted answers

alexnajm

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!

All comments

alexnajm

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703