up vote 0 down vote favorite
share [g+] share [fb]

What's the most efficient way to disable saving of fields with commas in an entire Excel file? One approach could be to use Data Validation, and the other could be to validate all fields using a macro when the file is being saved. Are there other ways? Which one is better and why?

link|improve this question
Why do you need to prevent commas being used? If you're saving as CSV then Excel should just automatically quote any field containing the field delimiter. – DMA57361♦ May 6 '11 at 12:53
1  
The CSV file saved is parsed by another program which I can't change; so my only option is to change the Excel file somehow so that it doesn't save a file at all (instead of saving it with quotes when commas exist). – Jaywalker May 9 '11 at 15:02
I see, the problem really lies in another program which cannot parse valid CSV files correctly, and that you're having to work around. That's a pain. I think data validation is the way forward tbh, catch the users as they edit (rather than a random "you can't save" when maybe they can't see why), but I don't have anything more specific... – DMA57361♦ May 9 '11 at 15:07
feedback

1 Answer

up vote 1 down vote

Do a global search and replace for commas before you save the file. If I am working on address data I just replace the comma with two spaces.

link|improve this answer
Could you give a solution which is "automated"? A macro or a piece of code for efficiently doing the same :) – Jaywalker May 9 '11 at 15:01
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.