ISNUMBER is not working properly

[michaelch2934]

I have column B with either a numerical value or a letter text. In column M, I need to use only the numerical value and regard the text as 0.

In M, I entered =IF(ISNUMBER(B5),B5,0)

But, when B5=7, it returns a 0. When B5 is a T, it returns a 0 as well.

Column B is formatted as a Number (0 decimal places).

What am I doing wrong? Thanks in advance!

 

[AhoyNC]

Most likely the 7 was entered as text and changing the cell format will not change it to a number.
Try:
=IF(ISNUMBER(B5+0),B5+0,0)

 

[michaelch2934]

By hokies, it worked. Thanks AhoyNC. Even though I had checked for Column B as Number before, this seems to have fixed the problem.

 

[jtakw]

Hi,

If you have IFERROR, you can also do this:

Book1
	B	C	M
5	7		7
6	Text		0
7	10		10
Sheet275

Cell Formulas
Range	Formula
M5	=IFERROR(B5+0,0)

B5 and B7 ( 7, 10 ) were entered as Text.

 

[michaelch2934]

Thanks. Yes, adding the +0 did the trick. Now, why didn't it work without it? Or is this just a glitch we have to always work around?

 

[jtakw]

Any math done to a Number stored as Text converts it to a Real Number, by adding 0, we're not changing the original intended value.
You can also use B5*1, or --B5, which does the same.

 

[Trebor76]

You can also use the VALUE function on numbers that are stored as text i.e.

VALUE(B5)

will convert a text number in cell B5 to its value.

 

[Joyner]

Or if you do convert the column to proper numbers / text (using Text to Columns) you could also just use:

=N(B5)

 

[michaelch2934]

That's helpful information. Thanks.

 

[michaelch2934]

Good to know. Thanks.