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" & i).Value >= 100 And Sheet2.Range("c" & i).Value < 1000 Then
Sheet3.Range("a" & k).Value = Sheet2.Range("a" & i).Value
Sheet3.Range("b" & k).Value = Sheet2.Range("b" & i).Value

Sheet3.Range("c" & k).Value = Sheet2.Range("c" & i).Value

k = k + 1
End If
Next

End Sub

please explain some more. You just want to format numbers in certain column or what? — elrado, Mar 17 '12 at 17:55
I don't see this at 13 but rather at the 15th digit. Do you want the result for display or to do math on? is saving as a text string and converting to a value to use it possible? — datatoo, Mar 17 '12 at 21:11

score 31 · Accepted Answer · answered Mar 17 '12 at 19:18

31

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

answered Mar 17 '12 at 19:18

datatoo

2,019
2
21
28

I'm trying to do this for a particular sheet in a specific workbook by `Workbooks("B.xlsm").ActiveSheet.Columns("1").NumberFormat = "0"` & this doesnt seem to do it for me. Any ideas? – user248884 Oct 05 '17 at 23:12

score 1 · Answer 2 · answered Jul 16 '21 at 07:31

1

ActiveWorkbook.Worksheets("NON SMTF").Columns("C:C").NumberFormat = "@" use this working proper

answered Jul 16 '21 at 07:31

Ajit Kurmi

11
1

score 1 · Answer 3 · answered Mar 17 '12 at 20:34

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" & k).Value = "'" & Sheet2.Range("c" & 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 >= 100 And rCell.Value < 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

score 0 · Answer 4 · answered Apr 18 '14 at 20:43

0

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) <= "13" Then
'do something
End If

hope that helps. Cheers

answered Apr 18 '14 at 20:43

Nabspot

13
4

How to format column to number format in Excel sheet?

4 Answers4

Linked