Format number to include text in VBA

Question

i feel like I've searched high and low for answers to this, what feels like an easy issue, with no luck.

I am trying to format a number in VBA to include a specific text, just like i can do in excel.

e.g. i have a number 3, which i want to format to show "Workday 3"

Excel: "Workday" Standard = Workday 3

Example 1: Range(A1)=Format(MyNumber, "a #") = a 3

Example 2: Range(A1)=Format(MyNumber, "# Workday") = 4 Workday

Issue: Range(A1)=Format(MyNumber, "Workday #") = 3ork2a2

Thanks!

Answer 1

w, d and y are special characters within number formatting. You can escape them with the \ to display them as a literal character.

Range("A1").Value = Format(myNumber, "\Work\da\y #")

More detail from the Format documentation:

To display a character that has special meaning as a literal character, precede it with a backslash (\)... Examples of characters that can't be displayed as literal characters are the date-formatting and time-formatting characters (a, c, d, h, m, n, p, q, s, t, w, y, /, and :)...

Note, Format returns a String - so you could just do the following:

Range("A1").Value = "Workday " & myNumber