Need help with replacing characters

[jesuisbo]

Hi all,

Looking for some help with the following; 

I'm looking to replace commas in this string but NOT the ones that are between " ". 

So basically only the ones that are green in the below string. Any thoughts? :-)

2020-12-01,Donderdag,https://open.spotify.com/track/3uT4d8m0FfQRouoav48ra6,67,"Kris Kross Amsterdam,Emma Heesters","https://open.spotify.com/artist/4LcUpNlXFEleaLlelmkv2R,https://open.spotify.com/artist/5wV3FoaNbDB6X...",33

[DataNath]

@jesuisbo is there a more precise pattern/identifier here? Perhaps based on the start of the URLs up to the final " or something? As it stands, using your current pattern wouldn't get rid of the first comma in the second example (just before the Spotify URLs), as it sits between two double quotes.

[kimlam]

I think you can parse out the text in the double quotes first then replace the commas.

[BS_THE_ANALYST · Accepted solution]

@jesuisbo I'd use Text to Columns with the Ignore Delimiter in Quotes option. This will allow you to retain the comma in the quotes. Once you have each part of the text in it's own column, you can either concatenate all of them back together using a Formula tool: col1+col2 etc 

or you could use a Transpose+Summarize tool combo to concatenate. I've attached the workflow:

Text to columns config:

[jesuisbo]

Thank you @BS_THE_ANALYST this works! Had no clue I could ignore in quotes :-)) learning something new each day.

Appreciate the help!

[BS_THE_ANALYST]

@jesuisbo Thanks for the question! Got me digging around the tools a little deeper than normal. All the best. Always learning! 😎