|
=TEXT()
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 |
=TEXT()
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 |
=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 |
=TEXT()
If I have number like this 222,333,11
then it does not display commas "Lincoln Burrows" wrote in message ... 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 |
=TEXT()
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(ISNUMBE R(FIND(".",A1)),REPLACE(REPT("0",LEN(TRIM(SUBSTITU TE(A1,CHAR(160),"")))),FIND(".",TRIM(SUBSTITUTE(A1 ,CHAR(160),""))&"."),1,"."),REPT("0",LEN(TRIM(SUBS TITUTE(A1,CHAR(160),"")))))) -- Rick (MVP - Excel) "Lincoln Burrows" wrote in message ... 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 |
| All times are GMT +1. The time now is 06:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com