TEXT

[Paul]

I want to create a string that is always the same length
whether the original value is negative or positive.

using =TEXT(A1,"000.0000") returns a string that is 8
characters when positive and 9 characters when negative.
I do not want the length to vary.

????

 

[Peo Sjoblom]

How would you distinguish between positive and negative, assume you have

111.1234

in A1, how would you want that to differ with

-111.1234

?

maybe you want

=TEXT(ABS(A1),"000.0000")

or remove a decimal

=IF(A1<0,TEXT(A1,"000.000"),TEXT(A1,"000.0000"))



--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Stephen Bye]

In your number format, you have only specified a format for positive
numbers, so Excel will use that format preceded by a minus sign for negative
numbers.
You will need to specify explicitly the format that you want to use for
negative numbers, e.g.:
=TEXT(A1,"000.0000;-00.0000")

 

[Peo Sjoblom]

Actually this would be better assuming you want to chop off a decimal

=TEXT(A1,"000.0000;000.000")

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)