Format Cells

[Marc]

=TEXT(A1,IF(ISNUMBER(FIND(".",A1)),REPLACE(REPT("0",LEN(TRIM(A1))),FIND(".",TRIM(A1)&"."),1,"."),REPT("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]

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]

Thanks Dave for your quick action

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.