-2

I need to convert the column in excel to Number(Integer) datatype from General in Excel document.

I get data from external system which is displayed as "1E+14" and needs to change it to "100000000486422".

Sourabh
  • 9
  • 1

1 Answers1

0

If the numbers are correctly exported from the 'external system' then your column widths and/or number format are insufficient for Excel to display the number correctly. Widen the column widths to accommodate the 15 digits and use a cell number format of the Number type or custom number format of 0.

Note that Excel has a 15 digit floating point precision limit and will 'round' 16 digits to what it perceives as the 15 most important. Example: 100000000486422*9* becomes 1000000004864220.

See Floating-point arithmetic may give inaccurate results in Excel for more info.

EDIT:

As per the comments, I would recommend using bulk operations instead of looping through cells for individual changes.

Private Sub CommandButton1_Click()
    With ThisWorkbook.Worksheets("Sheet1")
        With .Columns("AI:AI").SpecialCells(xlCellTypeConstants, 1)
            .Cells.NumberFormat = "0"
        End With
        .Columns("AI:AI").EntireColumn.AutoFit
    End With
End Sub

That should resolve any time delay (aka looping) issues you experience but there may be larger underlying problems. Post back with a more complete scope of any problems you continue to experience.

  • Private Sub CommandButton1_Click() With ThisWorkbook.Worksheets("Sheet1") Dim Rng As Range Dim x As Long Dim countRows As Long Set Rng = Range("AI1:AI65536" & Range("A65536").End(xlUp).Row) For x = 1 To Rng.Rows.Count Step 1 Rng.Cells(x, 1).NumberFormat = "0" Next x End With End Sub – Sourabh Jan 15 '15 at 14:14
  • I am using above script for my requirement and its fulfilled my requirement but problem is it gets run for entire column. what if i have to run it for only rows which have data populated in it? – Sourabh Jan 15 '15 at 14:18
  • @Sourabh - Check to see if Excel thinks the cells are truly blank. Use Ctrl+↑ to see if the active cell actually stops on a cell that looks blank. If it does not, you need to use Data ► Text-to-Columns ► Fixed Width ► Finish on each of the cells to make those cells truly blank. see [Zero Length String Microsoft Access](http://stackoverflow.com/questions/25830884/zero-length-string-microsoft-access#25848174) –  Jan 27 '15 at 13:27