Replacing commas in text fields, file comma separeted

[Sailor]

Hi 

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

[Kenda · Accepted solution]

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.

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