Keeping/inserting leading 0's

Question

I'm struggling to get and keep leading 0's in my project. Here are the currently relevant pieces of code:

Dim jobNum As String

 jobNum = Left(r1.Cells(1, 1), 6)

 r2.Cells(1, JOBCOL) = (jobNum)
 r2.Cells(1, JOBCOL).NumberFormat = "@"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Stuff I've tried -

 r2.Cells(1, JOBCOL) = CStr(jobNum)

- trying to keep it in string mode, because maybe it's pasting it that's the issue?

 If Len(jobNum) = 4 Then
  jobNum = "'00" & jobNum
  Else
  jobNum = jobNum
  End If

Ok, ok, if I can't get it to read it properly, maybe I can manually add it in? (expected length is 6, if the length registers as 4 I have an issue, and I need to add in two leading 0's)

And finally, my attempt to get it to read properly in the first place:

Dim Jobnum as String
Dim Jobnumfixed as Integer

jobNum = Left(r1.Cells(1, 1), 6)
jobnumfixed = format (jobnum, "000000")

I took all of these from Excel VBA Won't Keep Leading Zeroes . However, none of them have ended up with the expected result, which is to get and keep some leading 0's in my sheet. What am I doing wrong? Where should I be looking to fix this?

Thank you

Edit: Managed to get it to work. jobNum = Left(r1.Cells(1, 1), 6) became jobNum = ("'" & Left(r1.Cells(1, 1), 6)), and it all works now. Hurray!

Do you need the jobnum to be a number or text? If text, take @Sorceri's suggestion of formatting the cells as text before entering the data. If numbers, simply use custom formatting with "000000" to display each number padded to 6 digits with leading zeroes (4 becomes 000004 and 123 becomes 000123). — Blackhawk, Jan 26 '17 at 21:57
...and when I say "custom formatting", I don't mean the `Format` function, but instead the format dropdown on the "Home" tab in the ribbon. — Blackhawk, Jan 26 '17 at 21:58
added the line r2.Cells(1, JOBCOL).NumberFormat = "@" - still not getting there. Ditto with going to the home tab and changing the default format. Edit: I got it working! I added in ("'"&...) to the start of my jobnum=, and it all works now — Selkie, Jan 26 '17 at 21:59

Gary's Student · Accepted Answer · 2017-01-26T22:05:10.590

4

Here is a way to place leading zeros into a number:

Sub LeadingZerosNumbers()
    Dim N As Long

    N = 1137

    Range("A1").Value = N
    Range("A1").NumberFormat = "000000"
End Sub

and here is a way to place leading zeros into text that looks like a number:

Sub LeadingZerosText()
    Dim N As Long

    N = 1137

    Range("A1").NumberFormat = "@"
    Range("A1").Value = "00" & N

End Sub

In both cases A1 will display:

edited Jan 26 '17 at 22:05

answered Jan 26 '17 at 22:02

Gary's Student

95,722
10
59
99

Thank you! jobNum = ("'" & Left(r1.Cells(1, 1), 6)) managed to get it to work – Selkie Jan 26 '17 at 22:04

Keeping/inserting leading 0's

1 Answers1