Help needed with replacing characters

[jesuisbo]

Hi all,

I'm looking for some help with the following; 

I'd like to replace the green-colored commas BUT NOT the red-colored commas. This is because they should stay concatenated when using text-to-column for this CSV file.

The commas that you'll find within "  " should not be changed.

2020-12-01,Donderdag,https://open.spotify.com/track/3uT4d8m0FfQRouoav48ra6,67,"Kris Kross Amsterdam,Bilal Wahib,Emma Heesters","https://open.spotify.com/artist/4LcUpNlXFEleaLlelmkv2R,https://open.spotify.com/artist/5wV3FoaNbDB6X9INuQvs1K,https://open.spotify.com/artist/3898xesz6JuQkpz7Kiu4uM",33,67,41,47498,2020-10-21,2020-10-22,81,"Kris Kross Amsterdam,Yuyu van Scheppingen,Joren van der Voort,Bas van Daalen,Joey Ferre","Paul Sinha,B9NNY,Bas van Daalen,Bilal Wahib,Emma Heesters,Joey Ferre,Joost Theo Sylvio Yussef Abdelgalil Dowib,Jordy Huisman,Joren van der Voort,Sander Huisman,Yuki Kempees",Top Notch Music BV

any help is much appreciated!

[markcurry]

Hi @jesuisbo 

The following RegEx should be able to identify the comma's that aren't in quotes:

(,)(?=(?:[^"]|"[^"]*")*$)

See here: https://regex101.com/r/HlhorY/1

You can then replace those commas with a | for example, the use the Text to Columns to split by the |  

See the attached workflow, hopefully that does the trick for you..