Importing CSV into Excel with commas in quoted fields

Question

I have a CSV file that contains a text field that can have commas in it. When importing it into Excel, it's picking up these commas as field separators. I have the field in quotes, and I've tried double quotes as well. Is there any way I can import this as is or should I convert it to another delimiter?

Answer 1

Make sure there a no spaces between your separated values and wrap each field in quotes:

"1, 2","3, 4"

Will import into 2 cells - the first having "1, 2" and the second having "3, 4"

Answer 2

How are you importing it? Are you using the data import function on the ribbon or is this via VBA? Because when I import using the ribbon function I can specify the text qualifier as a " and it would import "abc,123","abc,123","abc,123" as abc,123<next cell>abc,123<next cell>abc,123.

If that isn't working though and you don't have to do this for dozens of files you could do a find-and-replace of "," (including quotes) and replace with "^t"(including quotes) (^t being the MS word version of tab if you do the find and replace in word). Then you can have a tab delimited file...