CODEWITHSUNDEEP
vbaexcel
khert87
khert87

Reputation: 21

remove zero's at the beginning of a string of numbers VBA

I have a column of numbers and I need to remove the leading zero's from each cell in the column if the numbers begin with zero's.

Upvotes: 0

Views: 5116

Answers (3)

Dammer15
Dammer15

Reputation: 190

I'm sure you can figure out how to iterate through each cell so I'm not going to demonstrate that in the code example below. This will do the trick. Replace A4 with each cell you need to process. 

Dim My_Number As String
Dim i As Integer
My_Number = Range("A4")
For i = 1 To Len(My_Number) - 1
    If InStr(1, My_Number, "0") = 1 Then
        My_Number = Right(My_Number, Len(My_Number) - 1)
    Else
        Range("A4") = My_Number
        Exit For
    End If
Next

Instr is looking for a zero in the fist position of the string. If if finds a zero then the next line writes all the string characters besides the first one(the zero) to the cell.

Upvotes: 1

user4039065
user4039065

Reputation:

Try,

with worksheets("Sheet1")
    with .columns(1)
        .numberformat = "General"
        .texttocolumns destination:=.cells(1), _
                       datatype:=xlFixedWidth, fieldinfo:=Array(0, 1)
    end with
end with

Upvotes: 2

Dan Donoghue
Dan Donoghue

Reputation: 6206

If converting to numbers isn't a viable solution for you then this UDF will work:

Function StripLeadingZeros(MyString As String)
Dim X As Long
For X = 1 To Len(MyString)
    If Left(MyString, 1) = "0" Then
        MyString = Right(MyString, Len(MyString) - 1)
    Else
        Exit For
    End If
Next
StripLeadingZeros = MyString
End Function

Upvotes: 0

Related Questions