Replacing commas in text fields, file comma separeted

Sailor

I have tried different solutions without a solid result. I would appreciate some help from the excellent community

Data:

Column1, Column2, Column3, Column4, Column5
,,"Street no 2, City", "Country"

Desired Output:

,,"Street no 2 City", "Country"

I want to remove any commas within quoted text. The quotes are not important for the result

Best regards Mats

Regex

Accepted answers

Kenda

Hey @Sailor! Could you use the Text To Columns tool with a comma as the delimiter and ignoring delimiters in quotes? Then, since everything will be in its own field, you could use a Formula tool with this Expression:

REGEX_Replace([Field3], ",", "")

to get rid of commas that were within quotes.

Split to Columns.PNG

Otherwise, just try adding a Formula from your Input with the following Expression:

REGEX_Replace([Field1], '("[^",]+),([^"]+")', "$1$2")

Hope this helps!

All comments

Kenda

REGEX_Replace([Field3], ",", "")

to get rid of commas that were within quotes.

Split to Columns.PNG

Otherwise, just try adding a Formula from your Input with the following Expression:

REGEX_Replace([Field1], '("[^",]+),([^"]+")', "$1$2")

Hope this helps!

JoeS

Hi Mats @Sailor

With regards to your data, is that how it looks if you open it in a text editor, or have you already got the data into one field in Alteryx.

The text to columns tool can be used to split the data out into multiple fields using the comma, you can specific to ignore delimiters in quotes.

Then use a multi-field formula to replace commas with nothing (AKA remove them)

If it isn't the above scenario it would be great if you could upload a sample of your data

Thanks

Joe

Edit: too slow, beaten to it above by @Kenda

Sailor

Thanks a lot, always easy when you see it

Mats

Sailor

Thanks @JoeS

Appreciate your effort

Regards Mats

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335