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

--
Message posted from http://www.ExcelForum.com

With VBA?

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

- Pikus


---
Message posted from http://www.ExcelForum.com/

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!


---
Message posted from http://www.ExcelForum.com/

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


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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


"couger77" wrote in message
...
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!


---
Message posted from http://www.ExcelForum.com/

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


---
Message posted from http://www.ExcelForum.com/

And how could we get the same thing to work with text? - Piku

--
Message posted from http://www.ExcelForum.com

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

"pikus" wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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?
Thanks again! - Piku

--
Message posted from http://www.ExcelForum.com

couger

If just for looks, FormatNumberCustom.

"00000000"(8 zeros and no quotes).

The numbers will still be 7 and 8 digits.

Gord Dibben Excel MVP

On Mon, 29 Dec 2003 17:02:35 -0600, couger77
wrote:

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!


---
Message posted from http://www.ExcelForum.com/

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?


---
Message posted from http://www.ExcelForum.com/

Thanks so much!


---
Message posted from http://www.ExcelForum.com/