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!

Answer 1

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: