Concatenate variable with leading zeroes with another string

Question

Scenario

I have an excel cell that contains value with leading zereos (Eg: 0002). My macro is copying this value to a variable called runNumber and pasting it into another file by concatenating this runNumber with some other string. But when it does that, I am missing the leading zeros of runNumber

Codes

yearInYy = "19"

ciNumber = "PFTPA-" & yearInYy & "-" & runNumber

Output cell showing as PFTPA-19-2

What I need

I need the output cell to show

PFTPA-19-0002

Anyone knows how to do it?

Answer 1

ciNumber = "PFTPA-" & yearInYy & "-" & Format(runNumber, "0000")

Using the Format function, you can convert a date or number to a string with a specific format. All we do here is require 4 digits, with leading zeros.

The equivalent in Excel itself is the TEXT function

Answer 2

In order to add leading zeroes to a string, you don't need VBA, you can use following Excel formula for this:

=CONCATENATE(REPT("0";4-LEN(D4));D4)

It works as follows:

• Take the length of your string, and subtract this from 4 (it will give you the number of "0" characters you need).
• Create a string, which consists of a repetition of "0" characters.
• Concatenate that repetition of strings to your original string.

Answer 3

Assuming the field containing the 0002 is formatted as a text field then work with strings throughout. Here I am reading runNumber from cell A1. This means you can deal with a variable number of leading zeroes.

Option Explicit
Public Sub test()
    Dim runNumber As String, yearInYy As String, ciNumber As String
    yearInYy = "19"
    runNumber = [A1]
    ciNumber = "PFTPA-" & yearInYy & "-" & runNumber
    Debug.Print ciNumber
End Sub