=TEXT()

[Lincoln Burrows]

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

 

[Rick Rothstein]

I'm not completely sure what you are after, but =F6 would seem to work.

 

[OssieMac]

Hi Lincoln,

=TEXT(F6,"#####.000")

or

=TEXT(F6,"00000.000")

 

[Lincoln Burrows]

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

 

[OssieMac]

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")

 

[Lincoln Burrows]

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

 

[Rick Rothstein]

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 Rothstein]

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)


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,"."))

 

[Lincoln Burrows]

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

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)


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,"."))

 

[Marc]

If I have number like this 222,333,11
then it does not display commas

 

[Rick Rothstein]

It looks like some of those "spaces" are not really spaces, but rather
characters with ASCII codes of 160. You usually get them by copying from a
webpage. This formula should handle the problem...

=TEXT(TRIM(SUBSTITUTE(A1,CHAR(160),"")),IF(ISNUMBER(FIND(".",A1)),REPLACE(REPT("0",LEN(TRIM(SUBSTITUTE(A1,CHAR(160),"")))),FIND(".",TRIM(SUBSTITUTE(A1,CHAR(160),""))&"."),1,"."),REPT("0",LEN(TRIM(SUBSTITUTE(A1,CHAR(160),""))))))