TEXT

  • Thread starter Thread starter Paul
  • Start date Start date
P

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.
 
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)
 
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
 
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.
 
Ken Wright said:
Just a thought, but how about a slight variation on Bobs reply:-

=IF(A2<0,TEXT(A2,"000.000"),TEXT(A2,"0000.000"))
Click to expand...
....

Or

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

if spaces would be considered significant characters.
 
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")
 
Back
Top