Truncate all leading zero's

[FryGirl]

I have a few thousand cell of text which all are preceded with zero's.

How can I get rid of the zero's?

I want to go from:
000045070J
000141000J

To:
45070J
141000J

 

[Andrew Poulsom]

With a formula?

=LEFT(A1,LEN(A1)-1)+0&RIGHT(A1,1)

 

[FryGirl]

Thank you Andrew...works grand.

 

[Rick Rothstein]

If you want to do this physically to the data within their cells, select a contiguous range of columns that contain your data and then run this macro...

Sub GetRidOfLeadingZeroes()
  Selection.Replace "0", " ", xlPart
  Selection = Evaluate(Replace("IF(@="""","""",TRIM(@))", "@", Selection.Address))
  Selection.Replace " ", 0, xlPart
End Sub

 

[FryGirl]

Thanks Rick...that works well also.