Remove left "0" zero numbers

[Panoos64]

Hi all, i would like to write a VBA code so that to run through col. "A" and should remove the left "0" (zero) numbers but NOT any in the middle or end of the code. Below in Sch. 1. are the original data and in Sch. 2. is the expected result. Many TIA.

Sch.1.

<tbody>
[TD="class: xl63, align: right"]00004[/TD]

[TD="class: xl63, align: right"]00022[/TD]

[TD="class: xl63, align: right"]00148[/TD]

[TD="class: xl63, align: right"]01706[/TD]

[TD="class: xl63, align: right"]01836[/TD]

[TD="class: xl63, align: right"]02079[/TD]

[TD="class: xl63"]

30748

Sch.2.​

<tbody>
[TD="class: xl65, align: right"]4[/TD]

[TD="class: xl65, align: right"]22[/TD]

[TD="class: xl65, align: right"]148[/TD]

[TD="class: xl65, align: right"]1706[/TD]

[TD="class: xl65, align: right"]1836[/TD]

[TD="class: xl65, align: right"]2079[/TD]

[TD="class: xl65, align: right"]30748[/TD]

</tbody>

​

[/TD]

</tbody>

 

[NeonRedSharpie]

Sub textToNumber()

    With Columns(1)
    
        For x = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
            tempStr = Cells(x, 1)
            Cells(x, 1).Value = Val(tempStr)
        Next x
    
    
    .NumberFormat = "0_);(0)"
    
    End With




End Sub

 

[Peter_SSs]

Hi all, i would like to write a VBA code so that to run through col. "A" and should remove the left "0" (zero) numbers but NOT any in the middle or end of the code. Below in Sch. 1. are the original data and in Sch. 2. is the expected result. Many TIA.

If the values in column A are numbers formatted as "00000" then

Sub RemoveEm_FormattedNumbers()
  Columns("A").NumberFormat = "General"
End Sub

If the values in column A are Text then

Sub RemoveEm_Text()
  With Columns("A")
    .NumberFormat = "General"
    .Value = .Value
  End With
End Sub

 

[Panoos64]

Hi Neon, thank you so much for your support. The code works perfect and nicely. Hv a great day!

 

[Panoos64]

Hi Peter, thanks for your support. It seemed that, the numbers are in text and so the second code adapt to my project and it works perfect. Thanks once again for your support. Hv a lovely day!

 

[Peter_SSs]

You are welcome.