Remove Commas on CSV through VBA

Hi Experts,

I'm faced with a problem. I copied my data and used the paste value function, pasted it onto another excel sheet and then saved as CSV. Ideally, there should be no formatting on the cells. However, column J threw some commas for which i am unable to understand.

All i want to do is put a control in place via VBA to remove all commas from the sheet.

Is there a VBA code that can perform this function?

Hi Aaron,
Thanks for calling me expert :-)

If its possible it's always better to check the source that populated the data.
Probably you need to update the field formatting to some digits without the comma for 1,000 and 1000,000.
But anyway, a macro to remove after is also not very complicated:

Steps:
1: import your data, split to columns (by seperator Comma)
2: select whole column J
3: open macro editor (VBE)
4: copy and paste this macro
5: run! (the comma will be replaced with nothing)

Please Login or Register  to view this content.

Hi,

Somehow this is not happening. Instead of selecting one column, can it be that the code runs on the whole sheet itself?
cause i would like to add some more codes like adding a column and header to the column.
and would this run on a .xls file type as well?

Hi Aaron,
Yes, the code works on the whole sheet, if you select more. But all commas will be removed.
If you would like to add more code, you'd better save this code to a module and store it in your personal.xlsb.
Then, you can always run the macro when you open a new csv of the same type.
If you want to save it with this workbook, you need to save it as '2003' version, or workbook 2007/2010 'with macros'

Sorry to bother you.

These are my steps below.
1) I am opening the CSV file,
2) Pasting the code in the VBA editor,
3) Selecting the column,
4) Alt F8 and Clicking Run

But the commas are not getting deleted.

Also, Is it possible to run the code without selecting any column? The entire sheet shouldn't have any column

Why can not you format the column in to "Text" using Excel's formats function?

I can do that. Ideally, paste special values should not have any formatting. However, I am not quite sure as to how I had commas in column J.
So, instead of manually going and formatting the text, I want to add a code which would remove all commas, Delete all blank cells and then add a column. Essentially, at the click of a button, all my formats, insertions would take place.

I know how to add a code for columns. I'm just looking for a code to remove all the commas in the file.

I do not believe a macro is faster than excel built-in function. Try to run a code on column J and use format function and see the difference.

Please Login or Register  to view this content.

Thanks..the code worked...