Copying Cells data, loses commas and changes formatting

Hello , i am trying to import an excel document and then copy over some columns to another worksheet from the imported worksheet, using a macro. Everything imports and copies over fine except for one column that has cells that comprise of a list of number separated by commas. When these cells get copied over some of the values retain the commas but some of them (specially when there are a lot values separated by comma) gets changed to this format

From: 1,229,124,012,441,230
To: 1.22912E+15

From: 1044,1048,1052,1053
To: 1.0441E+15

From: 1279,1282,1286,1295,1299
To: 1.27913E+19

From:926,929,938
To: 926929938

I am trying to figure out whats happening here! Is there a max number of letters this column can take and if it exceeds that value, excel converts them to this format? Or is the formatting gets changed to something else while copying and pasting?

Has anybody else experienced something similar? I have been scratching my head over this since this morning. Any ideas please?

Also if this is not the appropriate forum, please let me know or admins, can you please move this thread.

Thanks in advance for your time and help

How are you importing the data? If it is through the Data Import or Text Import Wizards then you should specify that the column that contains those values separated by commas is treated as Text.

Hope this helps.

Pete

Pete,
I am using this macro for importing the data. Can you please recommend a way to improve and incorporate that functionality?

Please Login or Register  to view this content.

I would suggest that after the line:

Please Login or Register  to view this content.

you just format the whole column with those composite values as text.

Hope this helps.

Pete