Macro to remove Leading 0s

**JimmiOO** · 10-27-2011, 08:33 AM

I have been using the below code to remove all my leading 0s, I have now run into a little problem.

The problem i have is once i have ran this macro to remove the leading 0s it auto formats the cells to numbers and one of my spread sheets is not picking up the numbers, is there away to adjust this code so i keep the original text format for my column?

Sub ptest() 
With Sheets("Sheet1").Columns("A:A")
 .NumberFormat = "0"
 .Value = .Value
 End With
 End Sub

**alansidman** · 10-27-2011, 08:42 AM

If you want to keep the original text and have a list of numbers in the next column, it is simply a case of in column B, type = A1*1. All text "numbers" will convert to real numbers, or am I mis-reading and you are looking to do something else.

so i keep the original text format for my column?

Alan

**Kelshaer** · 10-27-2011, 08:48 AM

Try this. If it works then please mark the thread as "Solved"

Sub ptest()
Dim i As Integer
Dim shtTest As Worksheet
Set shtTest = ThisWorkbook.Worksheets("Sheet1")
For i = 1 To shtTest.UsedRange.Rows.Count
    Do While VBA.Left(shtTest.Range("A" & i), 1) = "0"
        shtTest.Range("A" & i) = VBA.Mid(shtTest.Range("A" & i), 2, VBA.Len(shtTest.Range("A" & i)) - 1)
    Loop
Next
End Sub

**JimmiOO** · 10-27-2011, 08:58 AM

Alan i can use a different colum to extract the leading 0 but i would rather not do that if i can help it as this will be part of another fuction. All i want to do is hit a button and get rid of the leading 0s in a column keeping the original format which is text

Kelshaer

I have tried your option but i am getting a Run-Time error '6' Overflow, the column i am working on has over 62000 entires, that maybe the cause of this error?

**Kelshaer** · 10-27-2011, 10:27 AM

try this

Sub ptest()
Dim i As Long
Dim shtTest As Worksheet
Set shtTest = ThisWorkbook.Worksheets("Sheet1")
For i = 1 To shtTest.UsedRange.Rows.Count
    Do While VBA.Left(shtTest.Range("A" & i), 1) = "0"
        shtTest.Range("A" & i) = VBA.Mid(shtTest.Range("A" & i), 2, VBA.Len(shtTest.Range("A" & i)) - 1)
    Loop
Next
End Sub