How to delete all commas from a spreadsheet?

I have a large spreadsheet in Excel 2007. I am converting it to a .csv file to import in to another program. I need to delete all commas from all data.

When I try to replace all commas (with nothing or with another character), I get the error message "The formula you typed contains an error."

I have tried various formats (text, general, etc.) and various file types (.xls,.xlsx, .csv) and still get the same error.

Originally Posted by contaminated

That's because your formulas argument separator is also comma. Try..

1. Change your location in Regional and Language Setting dialog box, in Control Panel, to any other country say Russia. This will change default arqument separator in formulas
2. Than do your Find & Replace as usually.
3. Reset to your location in Regional and Language Setting dialog box

Maybe it's the silliest way, but I'd do that

I just tried doing that but unfortunately it didn't work.

Originally Posted by shg

Try Edit > Go to > Special, Constants.

Then find and replace comma with nothing.

The spreadsheet is too large to select all constants. When I tried doing it for a single column, I still received the same error message.

Have you tried simply doing a global search and replace? In Excel 2003, just click Edit > Replace, then enter a comma in the "Find what" field and leave the "Replace with" field blank.

The global find and replace gives the same error message.

I have 50K rows and 60 columns. I will probably be doing this just once. I am somewhat familiar with macros (have modified, never written from scratch).

Thanks shg - the code worked!

Thanks dude. Magic code worked!