How to format column to number format in Excel sheet

Question

Below is the VBA code   Sheet2 contains all of the values in general format   After running the code  values in column  C  of Sheet3 contain exponential values for numbers which are 13 or more digits   What should be done so that column  C  of Sheet3 does not contain exponential values   private Sub CommandButton1 Click   Dim i  j  k As Variant   k   1 For i   1 To 30000 If Sheet2 Range  c   amp  i  Value  gt   100 And Sheet2 Range  c   amp  i  Value  lt  1000 Then Sheet3 Range  a   amp  k  Value   Sheet2 Range  a   amp  i  Value Sheet3 Range  b   amp  k  Value   Sheet2 Range  b   amp  i  Value  Sheet3 Range  c   amp  k  Value   Sheet2 Range  c   amp  i  Value  k   k   1 End If Next  End Sub

User · Accepted Answer

This will format column A as text  B as General  C as a number   Sub formatColumns    Columns 1  NumberFormat        Columns 2  NumberFormat    General   Columns 3  NumberFormat    0  End Sub

User · Answer

Sorry to bump an old question but the answer is to count the character length of the cell and not its value   CellCount   Cells Row  10  Value If Len CellCount   lt    13  Then  do something End If   hope that helps  Cheers

User · Answer

If your 13 digit  number  is really text  that is you don t intend to do any math on it  you can precede it with an apostrophe  Sheet3 Range  c   amp  k  Value        amp  Sheet2 Range  c   amp  i  Value   But I don t see how a 13 digit number would ever get past the If statement because it would always be greater than 1000   Here s an alternate version  Sub CommandClick        Dim rCell As Range     Dim rNext As Range      For Each rCell In Sheet2 Range  C1 C30000   Cells         If rCell Value  gt   100 And rCell Value  lt  1000 Then             Set rNext   Sheet3 Cells Sheet3 Rows Count  1  End xlUp  Offset 1  0              rNext Resize 1  3  Value   rCell Offset 0  -2  Resize 1  3  Value         End If     Next rCell  End Sub

[excel] How to format column to number format in Excel sheet?

Examples related to excel

Examples related to vba