Any Excel gurus here? Need help with a work problem and VBA.

[C47]

 I work with a lot of alphanumeric and numeric characters that look something like this...

00D184500

002A08800

00209100

00209200

0B3651300

07127400

07131000

0AF8230100

08310400

08310500

0H8310600

08701300

08741400

0T8741600

08741800

 

Up until now, they've only had leading zeros I need to remove, but now we're getting some with both leading and trailing zeros. What I need is a formula (preferably a VBA macro I can easily just shortcut to) to remove both the leading and trailing zeros from strings of data like this. These values would be formatted as text in the data sheets I'm working with. I was able to modify a formula I found online (see below) to remove leading zeros, so I really only need the trailing ones. Would it be possible to edit this into my working macro, or do I need to create a new macro for trailing?

 

Here's the macro I'm using to remove the leading zeros and it's working flawlessly...

 

Option Explicit

Sub removezeros()

 

Dim myCell As Range

Dim myRng As Range

Dim iCtr As Long

 

With Worksheets("Sheet1")

Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells

For iCtr = 1 To Len(myCell.Value)

If Mid(myCell.Value, iCtr, 1) <> "0" Then

Exit For

End If

Next iCtr

myCell.Value = Mid(myCell.Value, iCtr)

Next myCell

End With

 

End Sub

 

Anybody have any experience with VBA and can help me out here?