Removing Leading Zero's...

[mfronczak]

I have a long list of numbers in the format of 000072-98-4, 001330-60-1, etc.

I want to remove all leading zeros (so that the above would become 72-98-4 and 1330-60-1). What would the formula be for this?

Mike

[Jay Petrulis]

Hi,

The following UDF seems to work. I am sure that a regular function can be written, but it will be pretty long, I would imagine.

----------------------
Function StripLeadZero(reference_cell)
Dim x As Integer, first_pos As Integer

For x = 1 To Len(reference_cell)
On Error Resume Next
If Mid(reference_cell, x, 1) <> 0 Then
first_pos = x
Exit For
End If
Next x
On Error GoTo 0

StripLeadZero = Mid(reference_cell, first_pos, 255)
End Function
----------------

HTH,
Jay

[Damon Ostrander]

Hi mfronczak,

If you are willing to entertain a VBA macro-based solution, here is a simple macro that will do this. To install this macro, just paste this code into a macro module (Alt-TMV and paste into code pane). Then select all the cells that contain values you want the leading zeroes stripped from and run the StripLeadingZeroes macro.

Sub StripLeadingZeroes()
'Strips all leading zero characters from text strings in the
'selected cells
Dim Cell As Range
For Each Cell In Selection
Cell.Value = Rzero(Cell.Value)
Next Cell
End Sub

Function Rzero(ByVal StrIn As String) As String
If Left(StrIn, 1) = "0" Then
Rzero = Rzero(Mid(StrIn, 2))
Else
Rzero = StrIn
End If
End Function

[Jay Petrulis]

Damon,

Nice!!!!!

Much better than mine, as the OP would not need to hold data in another column, or do the usual Copy>PasteSpecial(Values)>Delete original song and dance.

Bye,
Jay

[Mark O'Brien]

Bloody CAS numbers!!!