Padding formatted numbers with leading spaces

[Don Wiss]

In VBA I'd like to format numbers with a fixed width. I want to pad blanks
to the front of the formatted number to get to the desired width. I know
there is a function that returns a desired number of spaces. What is it?
Then I can write a short function where I format the number, measure its
width, and then tack on the spaces needed. Or is there an easier way that
I'm missing? Like a string to give Excel's Format function that makes it
return a desired width?

Don <donwiss at panix.com>.

 

[Dave Peterson]

If you know how many spaces, just format it that way to begin with:
dim myStr as string
mystr = format(range("a1").value,"0000000000")
will give me up to 10 (if I counted correctly) leading zeros.

FYI:
There's a Space function for repeating the space character:
msgbox "hi" & space(12) & "there"

There's a string function to string a bunch of characters:
msgbox string(12, "a")
even 0's
msgbox string(12,"0")

So this may be easier to decipher later:

mystr = format(range("a1").value,string(10,"0"))

 

[Guest]

If you want to hard-code the range, you can select the range
click: Format>Cells>Number>Custom
In the Type: textbox enter 0 for the number of digits you want

Example:
0000000 in the Type box
will make 35 display as 0000035

to do this in VBA try the above with your recorder turned on.

 

[Don Wiss]

If you know how many spaces, just format it that way to begin with:
dim myStr as string
mystr = format(range("a1").value,"0000000000")
will give me up to 10 (if I counted correctly) leading zeros.

But I want leading spaces, not leading zeros. Then when I concatenate the
strings together I can get the numbers in the columns to line up. This is
what I came up with:

Function FmtPHG(N As Double) As String
' format hard coded to use with payroll by HG report
' arg: N - number to format

Dim S As String

If N = 0 Then
S = Space(8) & "- "
Else
S = Format(N, "##0.0%")
S = Space(10 - Len(S)) & S
End If

FmtPHG = S

End Function

Thanks for the Space() function.

Don <donwiss at panix.com>.

 

[Dave Peterson]

ahhh. I missed that (in your subject and in the body!)

S = Format(N, "0.0%")
s = right(space(10) & s, 10)

(or some variation) might work ok, too.

 

[Don Wiss]

If you want to hard-code the range, you can select the range
click: Format>Cells>Number>Custom
In the Type: textbox enter 0 for the number of digits you want

Example:
0000000 in the Type box
will make 35 display as 0000035

to do this in VBA try the above with your recorder turned on.

Except my question was about padding with spaces, not zeros.

Don <donwiss at panix.com>.

 

[Tom Ogilvy]

Your choice:

? "==>" & space(10) & "<==="
==> <===
? "==>" & String(10," ") & "<==="
==> <===