Removing Leading Zeros

  • Thread starter Thread starter Keith Budzynski
  • Start date Start date
K

Keith Budzynski

I am using MS Excel 2002 and trying to remove the leading
zeros from a file I have. Below is a sample of the
record.
000000B0BVS4
000000B0G8KQ
001000613486
001021492996
The came to me as a comma delimited csv file.

Thanks in advance,
Keith
 
I am using MS Excel 2002 and trying to remove the leading
zeros from a file I have. Below is a sample of the
record.
000000B0BVS4
000000B0G8KQ
001000613486
001021492996
The came to me as a comma delimited csv file.
Click to expand...

If you want to use formulas to do this and the sample records above were in
A1:A4, try the following array formula in B1.

=MID(A1,MATCH(FALSE,MID(A1,ROW(INDIRECT("1:32")),1)="0",0),64)

Fill B1 down into B2:B4. Select B1:B4, copy, select A1:A4, paste-special as
values, then delete B1:B4.

If you want to use a macro, select the cells from which you want to remove
leading zeros and run the following macro.


Sub foo()
Dim c As Range, n As Long, s As String

If Not TypeOf Selection Is Range Then Exit Sub

For Each c In Selection

If Not c.HasFormula And c.Value Like "0[!.]*" Then
s = c.Value
n = 1

Do While Mid(s, n, 1) = "0"
n = n + 1
Loop

c.Formula = "'" & Mid(s, n)
End If

Next c

End Sub
 
Back
Top