| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
=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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
=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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
=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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
=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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
=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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using TEXT and &TEXT - display numbers with commas, underline text | Excel Discussion (Misc queries) | |||
| Text does not display in "Text boxs" and when wrapping text in a c | Excel Discussion (Misc queries) | |||
| select text in cell based on text from another cell, paste the text at the begining of a thrid cell, etc... | Excel Programming | |||
| Excel VBA: Worksheet cell .Text property: 1024 bytes text len limit | Excel Programming | |||
| extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming | |||
Linear Mode
