Padding Numbers

  • Thread starter Thread starter couger77
  • Start date Start date
C

couger77

I need to know if it is possible to pad a number with a single digit (0
if and only if the that number is less than 8 digits long.

Thanks
 
With VBA?

x = Worksheets(1).Cells("A1").Value
If x < 10000000 Then
x = x * 10
Worksheets(1).Cells("A1").Value = x
End If

- Pikus
 
I have over 2,000 entries in column C that consists of 7 digit and 8
digit numbers. I need the ones with 7 digits to be 8 digits by placing
a zero in front of it. Am not familiar with VB. Please help.

Thanks!
 
Private Sub CommandButton1_Click()
Dim x As Long
Dim z As Long
Dim snum As String
Worksheets("Sheet1").Columns("C").NumberFormat = "@"
Do
x = x + 1
Loop Until Worksheets("Sheet1").Cells(x + 1, 3).Value = ""
For z = 1 To x
snum = Worksheets("Sheet1").Cells(z, 3).Value
If Len(snum) < 8 Then
snum = "0" & snum
Worksheets("Sheet1").Cells(z, 3).Value = snum
End If
Next z
End Sub

- Pikus
 
I forgot to say that you should put that code either in a Button or in
Macro (sans CommandButton1_Click()). I could show you how to hook i
up to a key on your keyboard if you'd like as well, but then I'd jus
be showing off. - Piku
 
I should also inform you that this will only run until it encounters th
first blank cell in column "C". We can fix that if need be. - Piku
agai
 
You could format the column with a custom format as "00000000"

or you could use this routine which will format the entire column as text
(no longer as numbers)

Sub testit()
Const intCol As Integer = 3
Dim rng As Range, col As Range

With Worksheets(1)
Set col = Range(.Cells(1, intCol), .Cells(Rows.Count,
intCol).End(xlUp))
col.NumberFormat = "@"
For Each rng In col
rng.Value = Format(rng.Value, "00000000")
Next
End With
End Sub

Rob
 
I like that. It's a lot less messy. You're teaching me some great
stuff here Rob. May I ask how you learned VBA? - Pikus
 
If you want to continue this discussion, please e-mail me directly - you'll
need to remove the junkmail bit from my e-mail address.

I learned VBA like anyone else. Try things and fail, read help, read groups,
improve. It takes time and I'm still learning new things (for example, I
didn't know about the "Type Of" operator until just a few days ago)
I intend to put a webpage together at some point soon.

Cheers
 
Thank you both. I will need to make it so that the script does not sto
when it hits a blank in the road.

So I just press alt-F11 to enter the VBA edit mode right?

Thanks again,

Jerem
 
I don't see your e-mail address Rob. Mind e-mailing me first? Mayb
you could let me know how to make that work with text?
(e-mail address removed) Thanks again! - Piku
 
couger

If just for looks, Format>Number>Custom.

"00000000"(8 zeros and no quotes).

The numbers will still be 7 and 8 digits.

Gord Dibben Excel MVP
 
This is not working for me. I get errors in VBA. Using the format cells
option will not work for me. Eventually thesee numbers will be
converted to a date using another formula.

Any ideas?
 
ACtually, it does not work still.
Here is the formula that is looking at the cell with 8 characters.

=DATE(RIGHT(D27,4),LEFT(D27,2),MID(D27,3,2))

If I just format it, the cell looks like it contains an extra zero, bu
it is just the same number when you click on it
 
How about

=DATE(RIGHT(TEXT(D27,"00000000"),4),LEFT(TEXT(D27,"00000000"),2),MID(TEXT(D27,"00000000"),3,2))

Jon
 
Back
Top