Split column using RegEx (string and special characters)
- 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 1 column of data that I require the Regex tool to split into chunks of 3 lettered string and split the special character that are in between.
What would the code be for known lengths of data and special characters?
Thanks
1 column data set | |||||||||
ANT/CAT*DOG | ANT | / | CAT | * | DOG | ||||
CAT/DOG | CAT | / | DOG | ||||||
ANT\CAT/DOG*BAT/RAT | ANT | \ | CAT | / | DOG | * | BAT | / | RAT |
Solved! Go to Solution.
- Labels:
- Expression
- Regex
- Tips and Tricks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @Sam7
Can approach it a couple ways, but my take was to use a (\W) expression to find special characters, and use a Regex replace to add spaces to either side of it. Text to columns can then split this out based on the spaces.
You could do text-to-rows and pivot to make it more flexible on the number of columns as well.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I would tokenize in the Regex Tool and use the expression,
\W|\w+
workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Sam7 ,
The regex is same as @PhilipMannering's solution . Added logic to make it dynamic i.e this works for different num of cols .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks @PhilipMannering and @benakesh
This is great. It worked.
