Keeping leading 0 in string - numeric string conversion
I am working on a macro for a document tracking sheet at work. I am using a button that prompts the user to enter a document number and I would like to provide a default number based on the following numbering convention. The first two characters of the document number are the last two-year digits (in this case 15), then there is a "-" followed by a five-digit serialization.
My current code is looking at the last document entered and incrementing those last 5 characters, but chopping off any leading zeros I want to keep. This is a code extraction to generate this default number (assuming the "prevNCRF" variable is the name of the previous document found in the document):
Sub codeChunkTester()
Dim prevNCRF, defNCRFNum As String
Dim NCRFNumAr() As String
'pretend like we found this in the sheet.
prevNCRF = "15-00100"
'split the string into "15" and "00100" and throw those into an array.
NCRFNumAr() = Split(prevNCRF, "-")
'reconstruct the number by reusing the first part and dash, then converting
'the "00100" to a number with Val(), adding 1, then back to a string with CStr().
defNCRFNum = NCRFNumAr(0) & "-" & CStr(Val(NCRFNumAr(1)) + 1)
'message box shows "15-101" rather than "15-00101" as I had hoped.
MsgBox (defNCRFNum)
End Sub
So can someone help me to store these zeros? I guess I could include a loop that checks the length of the string and adds a leading zero until there are 5 characters, but maybe there is a better way ...
source to share
Converting "00100"
to Double
with Val
turned it to 100
, so it CStr(100)
returns "100"
as it should.
You need to format the string so that it looks like this:
defNCRFNum = NCRFNumAr(0) & "-" & Format(Val(NCRFNumAr(1)) + 1, "00000")
If you need to parameterize the length of a string, you can use a function String
to generate a format string:
Const digits As Integer = 5
Dim formatString As String
formatString = String(digits, "0")
defNCRFNum = NCRFNumAr(0) & "-" & Format(Val(NCRFNumAr(1)) + 1, formatString)
source to share
This is the solution to the loop I mentioned above. If anyone has anything better, I'm all ears!
prevNCRF = "15-00100"
NCRFNumAr() = Split(prevNCRF, "-")
zeroAdder = CStr(Val(NCRFNumAr(1)) + 1)
'loop: everytime the zeroAdder string is not 5 characters long,
'put a zero in front of it.
Do Until Len(zeroAdder) = 5
zeroAdder = "0" & zeroAdder
Loop
defNCRFNum = NCRFNumAr(0) & "-" & zeroAdder
MsgBox (defNCRFNum)
source to share