Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove leading zeros | Excel Discussion (Misc queries) | |||
REMOVE LEADING ZEROS | Excel Worksheet Functions | |||
Remove leading zeros from column | Excel Discussion (Misc queries) | |||
How to remove leading zeros using excel 2000 | Excel Discussion (Misc queries) | |||
remove leading zeros from text strings | Excel Worksheet Functions |