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?

Sub ImportSheet()

Dim wsNEW As Worksheet, wbDATA As Workbook, fNAME As String

Do
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = "C:\Data"      'default path to open to
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xl*", 1  'default
        .Title = "Select a file to Import"
        .Show
        If .SelectedItems.Count > 0 Then
            fNAME = .SelectedItems(1)
        Else
            Application.ScreenUpdating = True
            Exit Sub
        End If
    End With

    Set wbDATA = Workbooks.Open(fNAME)
    ActiveSheet.UsedRange.Offset(1).Copy wsNEW.Range("A" & Rows.Count).End(xlUp).Offset(1)
    wbDATA.Close False
    wsNEW.Columns.AutoFit
Loop
End Sub

I would suggest that after the line:

    wsNEW.Columns.AutoFit

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

Hope this helps.

Pete