By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,398 Members | 947 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,398 IT Pros & Developers. It's quick & easy.

Need help padding numbers

P: 69
Hello all.

I posted this in the Visual Basic forum, but since it has to do with VBA and both Access and Excel share VBA (somewhat) I was hoping someone here would be able to help me...

I have a problem in an Excel spreadsheet. I have a column with numbers in it where I need to change the value to conform to a certain format.

For example I may have a set of numbers that look like this in the column:

12345
123456
1234567
12345678
00234567-000

I need them all to be changed to conform to this format:

XXXXXXXX-000

with leading zeros to fill in the gaps. So the above numbers would be converted to:

00012345-000
00123456-000
01234567-000
12345678-000
00234567-000

I would like to create the code in excel as a macro and attach it to a button to either fix all numbers in the column or fix one at a time based on active cell.

Any help will be greatly appreciated.

Thanks,
narpet
Dec 14 '06 #1
Share this Question
Share on Google+
2 Replies


P: 69
Never mind, I figured it out.

Thanks,
narpet
Dec 14 '06 #2

NeoPa
Expert Mod 15k+
P: 31,661
You could start off with something like this.
Care should be taken as Excel recognises the format you use as numeric (date) hence stripping the '-000' bit if found.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Sub Worksheet_Change(ByVal Target As Range)
  4.     Static blnProcessing As Boolean
  5.     Dim intPos As Integer
  6.  
  7.     If Not blnProcessing Then
  8.         blnProcessing = True
  9.         intPos = InStr(1, ActiveCell, "-")
  10.         If intPos > 0 Then ActiveCell = Left(ActiveCell, intPos - 1)
  11.         ActiveCell = Format(ActiveCell, "00000000\-\0\0\0")
  12.         blnProcessing = False
  13.     End If
  14. End Sub
You still need to decide where and when this should be activated as I did it for all numeric entries in the sheet. You can use ActiveCell.Address to see where you are.
Dec 14 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.