Replacing multiple spaces with a comma

[ThomasHoag]

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!!

[JohnJPS]

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!

[Kenda · Accepted solution]

What if you used \s\s+ instead of \s+ in your formula?

[ThomasHoag]

Hey guys, I worked it out, just missing a few elements in the regex

[ThomasHoag]

Thank you both for the help!!