Using VBA to remove leading zeros
 

Hi everyone

Just a quick question about VBA within Microsoft Excel 2002. Column A in
one of my sheets has a list of supplier codes in it. The problem is that
the codes are all prefixed with multiple zeros which I'd like to remove.
I have a code snippet which can do it for a field in Microsoft Access, but
I'm not sure how to use the same code to do it to a column in Excel.

Can anybody help me out? All I wanna know is how to apply VBA code to
columns in Excel the same way that I'd apply it to a field of a table in
Access.

Thanks very much,
Michael Thomas

Using VBA to remove leading zeros
 

Michael

Depends how many cells you need to do this to. If it's only a few
tens/hundreds then the code below will do it. If it's thousands then you
could consider using a blank cell to enter a 1 in and copy that, then use
paste specialvalues+multiply to 'kick' them into numbers. and then delete
the spare 1. Come back if that is a better route

Sub test()
Dim myCell As Range
For Each myCell In Range("A1:A19")
myCell.Value = myCell.Value * 1
Next myCell
End Sub

(Change the range to suit...could also be made dynamic if it changes each
time)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Michael G. Thomas" wrote in message
news:opsd7likm6oizlpb@michael...
Hi everyone

Just a quick question about VBA within Microsoft Excel 2002. Column A in
one of my sheets has a list of supplier codes in it. The problem is that
the codes are all prefixed with multiple zeros which I'd like to remove.
I have a code snippet which can do it for a field in Microsoft Access, but
I'm not sure how to use the same code to do it to a column in Excel.

Can anybody help me out? All I wanna know is how to apply VBA code to
columns in Excel the same way that I'd apply it to a field of a table in
Access.

Thanks very much,
Michael Thomas

Using VBA to remove leading zeros
 

Nick's version will work if the data is all numeric (not alpha numeric). Is
that the case?

--
Regards,
Tom Ogilvy

"Nick Hodge" wrote in message
...
Michael

Depends how many cells you need to do this to. If it's only a few
tens/hundreds then the code below will do it. If it's thousands then you
could consider using a blank cell to enter a 1 in and copy that, then use
paste specialvalues+multiply to 'kick' them into numbers. and then delete
the spare 1. Come back if that is a better route

Sub test()
Dim myCell As Range
For Each myCell In Range("A1:A19")
myCell.Value = myCell.Value * 1
Next myCell
End Sub

(Change the range to suit...could also be made dynamic if it changes each
time)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Michael G. Thomas" wrote in message
news:opsd7likm6oizlpb@michael...
Hi everyone

Just a quick question about VBA within Microsoft Excel 2002. Column A
in
one of my sheets has a list of supplier codes in it. The problem is
that
the codes are all prefixed with multiple zeros which I'd like to remove.
I have a code snippet which can do it for a field in Microsoft Access,
but
I'm not sure how to use the same code to do it to a column in Excel.

Can anybody help me out? All I wanna know is how to apply VBA code to
columns in Excel the same way that I'd apply it to a field of a table in
Access.

Thanks very much,
Michael Thomas

Using VBA to remove leading zeros
 

Yes, the data is alphanumeric unfortunately. I have the code already
though, all I need to know is how to use it. In Access, you can create a
module and then call the module using a query. Is there something similar
in Excel? Just wanna know where to put the code and how to run it.

Thanks,
Michael


On Sun, 12 Sep 2004 10:16:18 -0400, Tom Ogilvy wrote:

Nick's version will work if the data is all numeric (not alpha
numeric). Is
that the case?




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Using VBA to remove leading zeros
 

in the VBE, do insert Module

paste you code in the module.

To run it, go back to excel and do Tools=Macro=Macros, select your macro
in the dialog and click run. Or while there you can click options (with
your macro selected) and set an accelerator key.

--
Regards,
Tom Ogilvy

"Michael G. Thomas" wrote in message
news:opsd7000mmoizlpb@michael...
Yes, the data is alphanumeric unfortunately. I have the code already
though, all I need to know is how to use it. In Access, you can create a
module and then call the module using a query. Is there something similar
in Excel? Just wanna know where to put the code and how to run it.

Thanks,
Michael


On Sun, 12 Sep 2004 10:16:18 -0400, Tom Ogilvy wrote:

Nick's version will work if the data is all numeric (not alpha
numeric). Is
that the case?




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Using VBA to remove leading zeros
 

One way to get rid of leading 0's in alphanumeric data:

Option Explicit
Sub testme01()

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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Open your workbook
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and test it out--Tools|macro|macros...|Run

"Michael G. Thomas" wrote:

Yes, the data is alphanumeric unfortunately. I have the code already
though, all I need to know is how to use it. In Access, you can create a
module and then call the module using a query. Is there something similar
in Excel? Just wanna know where to put the code and how to run it.

Thanks,
Michael

On Sun, 12 Sep 2004 10:16:18 -0400, Tom Ogilvy wrote:

Nick's version will work if the data is all numeric (not alpha
numeric). Is
that the case?


--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

--

Dave Peterson