if worksheet function

  • Thread starter Thread starter supersonikk
  • Start date Start date
S

supersonikk

Hi all,

Is there a way to write a formula so that if a cell does not contain any
numbers, just text, it result as 0 number?

i.e.:

=if(C5<>number,0,C5)
 
Hi all,

Is there a way to write a formula so that if a cell does not contain any
numbers, just text, it result as 0 number?

i.e.:

=if(C5<>number,0,C5)
Click to expand...

Try
=IF(ISERROR(VALUE(c5)),0,VALUE(c5))
 
Give this a try...

=IF(MIN(FIND({0,1,2,3,4,5,6,7,8,9},C5&"0123456789"))<LEN(C5),C5,0)

Rick
 
supersonikk said:
Hi all,

Is there a way to write a formula so that if a cell does not contain any
numbers, just text, it result as 0 number?

i.e.:

=if(C5<>number,0,C5)
Click to expand...
What's wrong with =IF(ISTEXT(B1),"error message or blank",Formula)?
 
Just thought I'd point out that your question is not completely clear. I
assumed you wanted to check if there any digits in the text contained in C5
(for example, "123" or "Text 123 more text") whereas the other two who have
responded so far assumed that you were asking whether the contents of C5 a
pure number or not. For my own curiosity, which question were you actually
asking?

Rick
 
Thanks Rick,

What I'm after is, say the formula "=if(C5<>number,0,C5)" is written in cell
G5
- If cell C5 contents is "256", then the result in cell G5 would be 256
- If cell C5 contents is "asdf", then the result in cell G5 would be 0
- If cell C5 contents is "asdf256", then the result in cell G5 would be 0

Hope this is of assistance?

--
Thanks,
Marko


Rick Rothstein (MVP - VB) said:
Just thought I'd point out that your question is not completely clear. I
assumed you wanted to check if there any digits in the text contained in C5
(for example, "123" or "Text 123 more text") whereas the other two who have
responded so far assumed that you were asking whether the contents of C5 a
pure number or not. For my own curiosity, which question were you actually
asking?

Rick
Click to expand...
 
Hi Fred,

Thanks for the reply, I think this one is best for my current use.

--
Thanks,
Marko


Fred Smith said:
Try:

=if(isnumber(c5),c5,0)

Regards,
Fred.
Click to expand...
 
Hi

What about cases where cell C5 is formatted as text, but contains a numeric
string? When such cases don't exist, or when then you want 0 returned, then
the formula will be simple:
=SUM(C5)
 
Hi Matthew,

Thanks for the reply. I've used Fred's formula for the moment but am sure
I'll find your formula to good use in the other templates I'll be working on.
 
Back
Top