Replacing multiple spaces with a comma
- 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
Hi guys,
So I have some string data that looks like this:
New York 123456 BAR 1
and it's all in the same column. I want to separate each entry into its own column. It seems to me the most direct way to do this would be to basically create a CSV file by replacing the instances in which there are multiple spaces with a comma. I tried using this formula:
REGEX_Replace([RecordType], "\s+",",")
But that got me this:
New,York,123456,BAR,1
Which isn't exactly what I need...
Any hints?
Thank you!!
Solved! Go to Solution.
- Labels:
- Parse
- Preparation
- Regex
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Just a hint: if the second field is always an integer, as in your sample row, then the following can be used to combine the first two fields:
IIF(IsInteger(GetWord([fld],1)), GetWord([fld],0), GetWord([fld],0) + " " + GetWord([fld],1))
From there, you should be able to piece everything that's left onto the end.
Hope that helps!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
What if you used \s\s+ instead of \s+ in your formula?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey guys, I worked it out, just missing a few elements in the regex
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you both for the help!!
