if worksheet function

[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)

 

[matthew.clegg]

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)

Try
=IF(ISERROR(VALUE(c5)),0,VALUE(c5))

 

[Rick Rothstein \(MVP - VB\)]

Give this a try...

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

Rick

 

[plb2862]

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)

What's wrong with =IF(ISTEXT(B1),"error message or blank",Formula)?

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Fred Smith]

Try:

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

Regards,
Fred.

 

[supersonikk]

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

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

 

[supersonikk]

Hi Fred,

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

--
Thanks,
Marko

Try:

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

Regards,
Fred.

 

[Arvi Laanemets]

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)

 

[supersonikk]

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.