MrExcel Publishing
Your One Stop for Excel Tips & Solutions

IsText / Is Number help please


Posted by Niv on November 12, 2001 3:22 AM

I am trying to return a value in one cell where there is a number and/or Text in another cell. How do I create a formula that will ask for both options.
ie: =IF(ISNUMBER(G8),"False",F8) and =if(ISTEXT(G8),"False",F8)

Please help
Thankyou


Posted by Conor on November 12, 2001 3:46 AM


if(AND(isnumber(g8),istext(g8)), "false", F8)

this returns false is it is a number and text, however change the and to OR ifn u eant it to check for text or numneric...

Conor

Posted by Aladin Akyurek on November 12, 2001 7:58 AM

Niv,

Your question is a bit ambigous. To clarify:

In a clean worksheet,

activate/select A1, activate Format|Cells, and choose Text as format.

In A1 enter: 5 (after the above step)
In A2 enter: alpha
In A3 enter: 3

In B1 enter: =ISNUMBER(A1) [ copy down this till B3 ]
In C1 enter: =ISTEXT(A1) [ copy down this till C3 ]
In D1 enter: =A1+0 [ copy down this till D3 ]
In E1 enter: =ISNUMBER(D1) [ copy down this till E3 ]

In A1:E3 you'll see the following:

{"5",FALSE,TRUE,5,TRUE;
"alpha",FALSE,TRUE,#VALUE!,FALSE;
3,TRUE,FALSE,3,TRUE}

> I am trying to return a value in one cell where there is a number and/or Text in another cell. How do I create a formula that will ask for both options.

Your "formula" suggests applying to a cell either

=IF(OR(ISNUMBER(A1),ISTEXT(A1)),A1) [ enter this in F1 and copy down to F3 ]

or

=IF(AND(ISNUMBER(A1),ISTEXT(A1)),A1) [ enter this in G1 and copy down to G3 ]

Inspect the results in column F and G and try to conclude.

Othewise try to elaborate on the problem that you want to solve.

Aladin