TEXT

[Paul]

is it possible to set up the TEXT worksheet function so
that the string length it returns is the same wnether the
value it is translating is positive or negative??

my attempts result in an extra space (the minus sign)
whenever the value turns negative.

 

[Bob Phillips]

Paul,

This will ignore negatives if that is what you mean

=TEXT(A2,"000;000")

just change the format to your desired result

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Bob,

what i want is a standard string length while still
showing the sign of the value.

In your example i am using =TEXT(A2,"0000.000") which
results in what i want, with the exception that the string
length is 1 character longer when the value is negative.
I do not mind significant digits varying, but i do need a
standard - unchanging string length.

it just occurred to me that another formula refering to
the result of the one above would probably work

=left(TEXT(A2,"0000.000"),8) should do the trick

any ideas???

Paul

 

[Ken Wright]

Just a thought, but how about a slight variation on Bobs reply:-

=IF(A2<0,TEXT(A2,"000.000"),TEXT(A2,"0000.000"))

That will leave the - sign in but change the digits to keep the string length
the same.

 

[Harlan Grove]

Just a thought, but how about a slight variation on Bobs reply:-

=IF(A2<0,TEXT(A2,"000.000"),TEXT(A2,"0000.000"))

....

Or

=TEXT(A2," 000.000;-000.000; 000.000")

if spaces would be considered significant characters.

 

[Dave Peterson]

I'm not sure if dashes and spaces are always the same width in every font.

I think just reserving a place holder might be better:

=TEXT(A2,"_-000.000;-000.000;_-000.000")