Remove zeros from cell data

[Kaziglu Bey]

I have a column of information that comes to me with extra zeros tacked on the left side of the data. It is similar to:

002047
000301
000027

What I need is help inserting code in a macro that will make the information look like:

2047
301
27

It has to be in a macro as this information interferes with a current macro and needs be changed in the process.

The numbers can be as different as the ones above; where one cell may have two zeros and the next may have one that need removed.

Thanks!

[Norman Jones]

Hi Kaziglu,

Try:
'=============>>
Public Sub Tester003()
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
Const col As String = "A" '<<==== CHANGE

Set SH = ActiveSheet '<<==== CHANGE
Set rng = Range(col & "1:" & col & _
Cells(Rows.Count, col).End(xlUp).Row)

For Each rCell In rng.Cells
With rCell
.Value = CLng(.Value)
End With
Next rCell

rng.NumberFormat = "0"

End Sub
'<<=============


---
Regards,
Norman


"Kaziglu Bey" <[email protected]>
wrote in message
news:[email protected]...
>
> I have a column of information that comes to me with extra zeros tacked
> on the left side of the data. It is similar to:
>
> 002047
> 000301
> 000027
>
> What I need is help inserting code in a macro that will make the
> information look like:
>
> 2047
> 301
> 27
>
> It has to be in a macro as this information interferes with a current
> macro and needs be changed in the process.
>
> The numbers can be as different as the ones above; where one cell may
> have two zeros and the next may have one that need removed.
>
> Thanks!
>
>
> --
> Kaziglu Bey
> ------------------------------------------------------------------------
> Kaziglu Bey's Profile:
> http://www.excelforum.com/member.php...o&userid=36086
> View this thread: http://www.excelforum.com/showthread...hreadid=571396
>

[Kaziglu Bey]

Worked like a charm. Thanks a bunch.

Hi Kaziglu,

Try:
'=============>>
Public Sub Tester003()
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
Const col As String = "A" '<<==== CHANGE

Set SH = ActiveSheet '<<==== CHANGE
Set rng = Range(col & "1:" & col & _
Cells(Rows.Count, col).End(xlUp).Row)

For Each rCell In rng.Cells
With rCell
.Value = CLng(.Value)
End With
Next rCell

rng.NumberFormat = "0"

End Sub
'<<=============


---
Regards,
Norman


"Kaziglu Bey" <[email protected]>
wrote in message
news:[email protected]...
>
> I have a column of information that comes to me with extra zeros tacked
> on the left side of the data. It is similar to:
>
> 002047
> 000301
> 000027
>
> What I need is help inserting code in a macro that will make the
> information look like:
>
> 2047
> 301
> 27
>
> It has to be in a macro as this information interferes with a current
> macro and needs be changed in the process.
>
> The numbers can be as different as the ones above; where one cell may
> have two zeros and the next may have one that need removed.
>
> Thanks!
>
>
> --
> Kaziglu Bey
> ------------------------------------------------------------------------
> Kaziglu Bey's Profile:
> http://www.excelforum.com/member.php...o&userid=36086
> View this thread: http://www.excelforum.com/showthread...hreadid=571396
>