Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Cells
=TEXT(A1,IF(ISNUMBER(FIND(".",A1)),REPLACE(REPT("0 ",LEN(TRIM(A1))),FIND(".",TRIM(A1)&"."),1,"."),REP T("0",LEN(TRIM(A1)))))
I have an excel function to get number value from text formatted cells But sometimes it has 1/2 like values in cells. Then the output value displays like 39479 Is there any way to avoid this? Pls Help.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Cells
When you have the text 1/2 (not .5 and not Jan 2), you're formula boils down to:
=text("1/2","000") And =text() is one of those "forgiving" functions. If the first argument looks like a number, then excel will treat it like a number. And in your case, excel "sees" a special number--a date (January 2 of the current year (2008)). So excel returns the serial number for that date: 39449 (and =text() returns it as text (not a number). So you could avoid the problem by looking to see if the cell is numeric or text first: =if(istext(a1),"it's text",yourlongformulahere) But I don't have a guess how you would want "1/2" treated. Marc wrote: =TEXT(A1,IF(ISNUMBER(FIND(".",A1)),REPLACE(REPT("0 ",LEN(TRIM(A1))),FIND(".",TRIM(A1)&"."),1,"."),REP T("0",LEN(TRIM(A1))))) I have an excel function to get number value from text formatted cells But sometimes it has 1/2 like values in cells. Then the output value displays like 39479 Is there any way to avoid this? Pls Help.. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Cells
Thanks Dave for your quick action
"Dave Peterson" wrote in message ... When you have the text 1/2 (not .5 and not Jan 2), you're formula boils down to: =text("1/2","000") And =text() is one of those "forgiving" functions. If the first argument looks like a number, then excel will treat it like a number. And in your case, excel "sees" a special number--a date (January 2 of the current year (2008)). So excel returns the serial number for that date: 39449 (and =text() returns it as text (not a number). So you could avoid the problem by looking to see if the cell is numeric or text first: =if(istext(a1),"it's text",yourlongformulahere) But I don't have a guess how you would want "1/2" treated. Marc wrote: =TEXT(A1,IF(ISNUMBER(FIND(".",A1)),REPLACE(REPT("0 ",LEN(TRIM(A1))),FIND(".",TRIM(A1)&"."),1,"."),REP T("0",LEN(TRIM(A1))))) I have an excel function to get number value from text formatted cells But sometimes it has 1/2 like values in cells. Then the output value displays like 39479 Is there any way to avoid this? Pls Help.. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 format cells to date format | Excel Discussion (Misc queries) | |||
How do I format cells to show date in Australian not US format? | Excel Worksheet Functions | |||
how do i format a cell based on format of a range of cells? | Excel Discussion (Misc queries) | |||
want format cells alignment not format cells font style | Excel Discussion (Misc queries) | |||
Cells won't convert to number format, even after format/cells/num. | Excel Discussion (Misc queries) |