cardiaccat13
New Member
- Joined
- Aug 19, 2015
- Messages
- 2
Hello, I work with a lot of item numbers that are both numeric and aphanumeric that look something like this...
00D184500
002A08800
00209100
00209200
0B3651300
07127400
07131000
0AF8230100
08310400
08310500
0H8310600
08701300
08741400
0T8741600
08741800
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
Thanks for your help!
00D184500
002A08800
00209100
00209200
0B3651300
07127400
07131000
0AF8230100
08310400
08310500
0H8310600
08701300
08741400
0T8741600
08741800
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
Thanks for your help!
