Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|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

1 Answer 1

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.

share|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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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