I am trying to split a column with delimiter '-' but there are some field names with '-' in it as a part of the name which should not be split.
The delimiter is preceded and succeeded by ' - ' (spaces before and after the delimiter) which is how it should be split. but if I put in that condition in the text to columns tool, the field names with spaces are getting split as well.
games | games1 | games2 | games3 |
Shimizu S-Pulse - Cerezo Osaka | Shimizu S | Pulse | Cerezo Osaka |
Kawasaki Frontale - Shimizu S-Pulse | Kawasaki Frontale | Shimizu S | Pulse |
Shimizu S-Pulse - Urawa Reds | Shimizu S | Pulse | Urawa Reds |
Kashima Antlers - Shimizu S-Pulse | Kashima Antlers | Shimizu S | Pulse |
While the correct way would be:
games | games1 | games2 |
Shimizu S-Pulse - Cerezo Osaka | Shimizu S-Pulse | Cerezo Osaka |
Kawasaki Frontale - Shimizu S-Pulse | Kawasaki Frontale | Shimizu S-Pulse |
Shimizu S-Pulse - Urawa Reds | Shimizu S-Pulse | Urawa Reds |
Kashima Antlers - Shimizu S-Pulse | Kashima Antlers | Shimizu S-Pulse |
How should I resolve this issue?
Please find attached my effort so far for reference:
Solved! Go to Solution.
@HW1
I assume the "-" between Games is having space before and after.
Hi @HW1 ,
The best thing to do would be to replace the space dash space with a new delimiter not in your data with,
replace(games, ' - ', '|')
See workflow attached.