How to format a column to number format in Excel worksheet?

Question

Below is the VBA code. Sheet2 contains all the values ​​in a common format. After running the code, the values ​​in the "C" column of Sheet3 contain exponential values ​​for numbers that contain 13 or more digits.

What needs to be done so that the "C" column 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 

Answer 1

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 

Answer 2

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-bit number will ever pass the If statement, because it will always be more than 1000. Here is an alternative 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 

Answer 3

Sorry to hit the old question, but the answer is to count the length of the cell symbol, not its value.

 CellCount = Cells(Row, 10).Value If Len(CellCount) <= "13" Then 'do something End If 

hope this helps. Greetings