VBA to remove leading and trailing zeros from item numbers.

[cardiaccat13]

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!

 

[shg]

Re: Need help with VBA to remove leading and trailing zeros from item numbers.

Welcome to the board.

How about a UDF?

Function CCTrim(sInp As String) As String
  CCTrim = Replace(Trim(Replace(sInp, "0", " ")), " ", "0")
End Function

Row\Col	A​	B​	C​
1​	00D184500	D1845	B1: =CCTrim(A1)
2​	002A08800	2A088
3​	00209100	2091
4​	00209200	2092
5​	0B3651300	B36513
6​	07127400	71274
7​	07131000	7131
8​	0AF8230100	AF82301
9​	08310400	83104
10​	08310500	83105
11​	0H8310600	H83106
12​	08701300	87013
13​	08741400	87414
14​	0T8741600	T87416
15​	08741800	87418

 

[cardiaccat13]

Re: Need help with VBA to remove leading and trailing zeros from item numbers.

Welcome to the board.

How about a UDF?

Function CCTrim(sInp As String) As String
  CCTrim = Replace(Trim(Replace(sInp, "0", " ")), " ", "0")
End Function

Row\Col	A​	B​	C​
1​	00D184500	D1845	B1: =CCTrim(A1)
2​	002A08800	2A088
3​	00209100	2091
4​	00209200	2092
5​	0B3651300	B36513
6​	07127400	71274
7​	07131000	7131
8​	0AF8230100	AF82301
9​	08310400	83104
10​	08310500	83105
11​	0H8310600	H83106
12​	08701300	87013
13​	08741400	87414
14​	0T8741600	T87416
15​	08741800	87418

<tbody>
</tbody>

This works perfectly, thanks so much for the quick response!

 

[shg]

Re: Need help with VBA to remove leading and trailing zeros from item numbers.

You're welcome.

 

[JenniferMurphy]

Re: Need help with VBA to remove leading and trailing zeros from item numbers.

How about a UDF?

Function CCTrim(sInp As String) As String
  CCTrim = Replace(Trim(Replace(sInp, "0", " ")), " ", "0")
End Function

Shg, I did a search on removing trailing zeroes and this came up. Thanks. It works great and is much simpler than what I was trying.