rMOVE ZERO

[oleg_v]

HI
I need some help.
in colmn "b" there is a lot of numbers how can i remove from the number
first leter if the first leter in the number equals zero

thanks

[Tinbendr]

Before and after example, please.

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<<<< change to suit
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To Lastrow 'Lastrow to 1 Step -1

If Left$(.Cells(i, TEST_COLUMN).Value, 1) = "0" Then

.Cells(i, TEST_COLUMN).Value = Right$(.Cells(i, TEST_COLUMN).Value, _
Len(.Cells(i, TEST_COLUMN).Value) - 1)
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[macropod]

hi oleg,

Surely this is just a formatting issue. For example, '1234' will display as '01234' if the cell is formatted as '00000'. Simply changing the cell format to '0' or 'General' will remove any leading 0s.

The only cases I can think of where that wouldn't work is if the 'numbers' are stored as text or contain non-numeric characters. If the 'numbers' are stored as text, you can solve the problem by inserting a '1' into any cell, cutting it, then selecting the numbers in column B and using Edit|Paste Special|Values > Multiply. In the latter case, you could use a macro like:
Sub Demo()
Dim oCel As Range
On Error Resume Next
For Each oCel In ActiveSheet.Range("B:B").SpecialCells(xlCellTypeConstants, xlTextValues)
With oCel
If Left(.Value, 1) = "0" Then .Value = Right(.Value, Len(.Value) - 1)
End With
Next
End Sub
or
Sub Demo()
Dim oCel As Range
On Error Resume Next
For Each oCel In ActiveSheet.Range("B:B").SpecialCells(xlCellTypeConstants, xlTextValues)
With oCel
While Left(.Value, 1) = "0"
.Value = Right(.Value, Len(.Value) - 1)
Wend
End With
Next
End SubThe second macro deletes all leading 0s, whether there is one or many.