CODEWITHSUNDEEP
vbaexcelformat
Ugo Lfe
Ugo Lfe

Reputation: 767

How to format all number in excel

I use excel to store data, but some of these data are numbers of 13 characters and excel will automatically display it in scientific style.

I need excel to display it completely.

Example: 4.01924E+12 to 4019238159486

I want to do this in VBA and apply it for all numbers.

Upvotes: 0

Views: 80

Answers (2)

PeterH
PeterH

Reputation: 1040

Use the below:

Sub Macro2()
    Selection.SpecialCells(xlCellTypeConstants, 1).NumberFormat = "0"
End Sub

Upvotes: 2

QHarr
QHarr

Reputation: 84465

With your range selected in VBA use

 Selection.NumberFormat = "0"

You may need to loop ranges, testing with Int(cell / 1) * 1) = 0 first to ensure you are only formatting integers, and then applying the formatting. I am sure there are more efficient ways to test the entire worksheet and apply the formatting but this gives you the general idea.

You can't use MOD function with very large numbers as you will get an overflow. See here: Overflow with large numbers using MOD. The answer at that link also recommends a function by Chip Pearson which can be used.

So you would have code along the lines of:

Public Sub Formatting()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")   ' change as appropriate
    Set rng = ws.Range("A1").CurrentRegion

    With ws
        For Each cell In rng.Cells
            With cell
                If Not IsError((Int(cell / 1) * 1) = 0) Then
                    cell.NumberFormat = "0"
                End If
            End With
        Next cell
    End With

End Sub

Otherwise in sheet

Highlight cells to convert, then go to number tab, expand bottom right arrow, select custom and type 0 in to type.

Formatting

Upvotes: 2

Related Questions