Replace comma with pipe in .csv file

[fddekker]

I have searched the internet but have not find the proper solution; however it seems that several users have the same problem. My current client receives a .csv comma delimited files from the source. In these files, some fields contains commas that appear in a text file in double quotes. Editing takes place in Excel, and the files get re-saved and then uploaded to a different software system. In order not to manipulate the files other than making necessary changes, I would like to run a macro on the csv file to replace commas that serves as delimiters with pipes, and then remove the double quotes (leaving the commas that do not serve as delimiters). I know how to delete the double quotes, but am having problems with replacing the correct commas with pipes. The reason I would like to do this as an Excel macro, is because I would like to add it to a ribbon that already contains other macros for them.

I found this on VBA express (please let me know if this was solved here on Mr Excel):

In a similar posting on Stockoverflow, some users suggest Imports System, Text, RegularExpressions. Can this be imported into VBA code in Excel?

Any help approciated

 

[thisoldman]

I'm truly sorry if I led you down an unproductive path. My knowledge of VBA's regex is limited to "This is supposed to work."

Currently, my time to to hack at this problem is extremely limited – life has intervened. I'll keep at it but I would look at other solutions for now.

Again, I'm sorry.

 

[fddekker]

No need to apologize. I appreciated your help. Especially since I solved it. Your code was correct, VBA just needed a comma between the ^ and chr(34)
Here is the regex expression that works:

,(?=(?:[^,chr(34)]|char(34)[^,chr(34)]*char(34))*$)

Thanks again! And thanks to all the other's input.