Formatting Numbers with Plus or Minus Sign

[Gordon Bentley-Mix at news.microsoft.com]

I have a requirement that numbers entered into a TextBox in a UserForm be
formatting using a leading plus sign ("+") for positive numbers and minus
sign ("-") for negative numbers. In this particular instance the numbers are
meant to represent a percentage, so I'm using the FormatPercent function -
something like this:

FormatPercent(fcnExtractNumber(txtMargin.Value) / 100, 2)

where "fcnExtractNumber" is a function I've written to extract just the
numeric value from a string (because Word is absolutely crap about how it
handles formatted numbers).

This works find for negative numbers. Something like "-.5" results in
"-0.50%", as does "0.5%". (This is one instance where fcnExtractNumber comes
into play because, due to the percent sign, FormatPercent doesn't recognise
"-0.5%" as being numeric; fcnExtractNumber strips out the percent sign and
returns just "-0.5", which FormatPercent is quite happy with.)

However, I don't see any way to make FormatPercent or even the straight
Format function insert the plus sign. I'm perfectly willing to NOT use
FormatPercent if necessary - in fact, it's maybe a bit easier because I don't
have to do the whole "/ 100, 2" thing with Format - but I'm just wondering
how to specify a format with either function that includes the plus sign.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Jay Freedman]

Hi Gordon,

The Format function can do what you need without recourse to FormatPercent.

Format(fcnExtractNumber(txtMargin.Value) /100), "+0.00%;-0.00%")

The two sections of the format string, separated by the semicolon, apply to
positive and negative values respectively (see the help topic "Different Formats
for Different Numeric Values (Format Function)"). The plus and minus signs are
simply literals, and the percent signs both cause the value to be multiplied by
100 and appear as literals. So you still need the division by 100, but the
format strings take care of the rounding to two decimal places.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.

 

[Gordon Bentley-Mix at news.microsoft.com]

You little ripper!

That's exactly what I was looking for Jay. Thanks heaps! I know what I'll be
doing on Thursday. (It's nearly quitting time for today, and tomorrow is my
"business development" day - a.k.a. my
"skivving-off-to-play-guitar-and-lounge-in-the-sun-by-the-pool" day.)
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Gordon Bentley-Mix]

Thanks again Jay. I've implemented your solution and showed it to the
client, and it's been met with a resounding cheer of delight.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!