PDA

View Full Version : Adding a Zero



thomas.szwed
01-04-2008, 10:09 AM
I have a column in my spreadsheet full of many rows of different 3 digits numbers. I need to add a zero infront of these numbers to make them 4 digits long. The reason for this is i have another application that reads the data from this sheet but it doesn't like it unless there 4 digits long. Ive tried formatting the cells like this but it doesnt actually add a zero on there. I was thinking some vba code could do the trick and be put into a macro. Can anyone help? Many Thanks

lucas
01-04-2008, 10:14 AM
See if this kb entry by Aaron helps you.

http://vbaexpress.com/kb/getarticle.php?kb_id=946

Bob Phillips
01-04-2008, 11:03 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

If IsNumeric(.Cells(i, TEST_COLUMN).Value) Then _
.Cells(i, TEST_COLUMN).Value = "'" & Left$("0000", 4 - Len(.Cells(i, TEST_COLUMN).Value)) & _
.Cells(i, TEST_COLUMN).Value
Next i
End With
End Sub