Text to Columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Data Investigation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
No worries @gflanagan !
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.