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.

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"))




Don Wiss wrote:

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

On Mon, 10 Jan 2005, Dave Peterson wrote:

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.

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 wrote:

On Mon, 10 Jan 2005, Dave Peterson wrote:

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

If you want to hard-code the range, you can select the range
click: FormatCellsNumberCustom
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" wrote:

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.

On Mon, 10 Jan 2005, gocush wrote:

Don Wiss wrote:

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?

If you want to hard-code the range, you can select the range
click: FormatCellsNumberCustom
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.

Your choice:

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


--
Regards,
Tom Ogilvy

"Don Wiss" wrote in message
...
On Mon, 10 Jan 2005, gocush wrote:

Don Wiss wrote:

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?

If you want to hard-code the range, you can select the range
click: FormatCellsNumberCustom
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.