Excel 2000 Removing Leading zeros

[floydman80]

I'm pulling data from another source and am getting leading zeros in front of the street address. I have 65K rows to work with - any easy way to strip off the leading zeros? All the articles I find talk to keeping the 0's - I want to drop them.

Ex: 0000000709 ELLEN DR

Thanks
Floyd

 

[SkipVought]

Floyd,

I Can't give you a spreadsheet solution, but here's a User Function. Copy 'n' Paste into a Module and then use like any other function...

Function StripLeadingZeros(rng)
   For i = 1 To UBound(Split(rng, " "))
      If i = 1 Then
         StripLeadingZeros = Split(rng, " ")(i)
      Else
         StripLeadingZeros = StripLeadingZeros & " " & Split(rng, " ")(i)
      End If
   Next
End Function

Use the function thusly...
[tt]
=StripLeadingZeros(A1)
[/tt]
where A1 contains a mailing address



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[byundt]

Assuming that you don't have ZIP Codes to worry about, you could strip the leading zeros with the following VBA macro. The macro works on data in column A, and goes in a regular module sheet. It will strip leading zeros from a number in any position in the cell (beginning, middle or end). Numbers must be separated by spaces to be recognized, however.

Sub StripZeros()
Dim X As Variant, Y As Variant
Dim i As Long, j As Long
Dim addr As String
Dim rg As Range
Set rg = Intersect(ActiveSheet.UsedRange, Range("A:A"))
X = rg.Value
For i = 1 To UBound(X)
    If X(i, 1) <> "" Then
        Y = Split(X(i, 1), " ")
        For j = 0 To UBound(Y)
            If IsNumeric(Y(j)) Then Y(j) = Y(j) * 1
            If j <> 0 Then
                addr = addr & " " & Y(j)
            Else
                addr = Y(j)
            End If
        Next j
        X(i, 1) = addr
    End If
Next i
rg.Value = X
End Sub

To install a macro, ALT + F11 to open the VBA Editor, then use the Insert...Module menu item to create a blank module sheet. Paste the code there. ALT + F11 to return to the worksheet.

To run the macro, ALT + F8 to open the macro window, select the macro and click the Run button.

If this procedure doesn't work, then open the Tools...Macro...Security menu item, and change the setting from High to Medium.

 

[byundt]

Floyd,
Both Skip's function and my macro use the Split function, which is available in Excel 2000 and later.
Brad

 

[KenWright]

Assuming your data was all in the format you have listed, with the number being first and having a space immediately after it, then also assuming your data is in Col A starting A2, in B2 put the following and copy down:-

=VALUE(LEFT(A2,FIND(" ",A2)-1))&RIGHT(A2,LEN(A2)-FIND(" ",A2)+1)

Then just copy Col B and paste special as values

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

Ken,

BIG assumption! ;-)

Skip,

Be advised: Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[KenWright]

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[floydman80]

And the winner is .......... KenWright - you are da man - you take the prize - your assuptions are on target and the function works like a champ. I will be postig to another board that I use .

Floyd