Replacing multiple spaces with a comma

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

Regex

Parse

Preparation

Accepted answers

Kenda

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

All comments

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

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703