=TEXT()
Thanks for your quick action Rick
But if I enter a number with leading spaces 2323.0033
Then it displays like this 0002323.0 Can't we avoid this
Please help
"Rick Rothstein" wrote in message
...
I guess a general solution to handle text numbers with or without decimal
points would be...
=TEXT(A1,IF(ISNUMBER(FIND(".",A1)),REPLACE(REPT("0 ",LEN(TRIM(A1))),FIND(".",A1&"."),1,"."),REPT("0", LEN(TRIM(A1)))))
--
Rick (MVP - Excel)
"Rick Rothstein" wrote in message
...
If you know there will be a decimal point in the text number, then you could
use this...
=TEXT(A1,REPLACE(REPT("0",LEN(TRIM(A1))),FIND(".", A1&"."),1,"."))
--
Rick (MVP - Excel)
"Lincoln Burrows" wrote in message
...
Thanks OssieMac,
But if my number has 6 leading zeros there it would be truncated.
00000001234.98700 displays like 01234.987
Decimal zeros also truncated
That's why I used =TEXT(A1,REPT("0",LEN(TRIM(A1))))
Using that function I can display the above number like this 00000001234
but the decimal part is the problem
"OssieMac" wrote in message
...
Hi Lincoln,
Did you receive my previous post? Your correction suggests that either
you
did not receive it or it did not do what you want. Anyway just in case
you
did not receive it, here it is again.
=TEXT(F6,"#####.000")
or
=TEXT(F6,"00000.000")
--
Regards,
OssieMac
"Lincoln Burrows" wrote:
Recorrection
00353.001 =TEXT(F6,REPT("0",LEN(F6))) 000000353
00353.001 =TEXT(F6,"0") 353
00353.001 =TEXT(F6,"0.000") 353.001
00353.001 ??? 00353.001
Pls any one tell me what is the text function to the last output
"Lincoln Burrows" wrote in message
...
IN FUNCTION OUT
00353.001 =TEXT(F6,REPT("0",LEN(F6))) 000000353
00353.001 =TEXT(F6,"0") 353
00353.001 =TEXT(F6,"0.000") 353.001
00353.001 ??? 00353.001
Pls any one tell me what is the text function to the last output
|