Search code examples
excelvbastring-concatenation

Concatenate variable with leading zeroes with another string

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?

Solution 
  • 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