| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if worksheet function
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) -- Thanks, Marko |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if worksheet function
On Apr 8, 11:37*am, supersonikk
wrote: 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) -- Thanks, Marko Try =IF(ISERROR(VALUE(c5)),0,VALUE(c5)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if worksheet function
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. -- Thanks, Marko " wrote: On Apr 8, 11:37 am, supersonikk wrote: 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) -- Thanks, Marko Try =IF(ISERROR(VALUE(c5)),0,VALUE(c5)) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if worksheet function
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" wrote in message ... 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) -- Thanks, Marko |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if worksheet function
supersonikk wrote:
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)? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if worksheet function
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" wrote in message ... 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) -- Thanks, Marko |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if worksheet function
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)" wrote: 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" wrote in message ... 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) -- Thanks, Marko |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if worksheet function
Try:
=if(isnumber(c5),c5,0) Regards, Fred. "supersonikk" wrote in message ... 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) -- Thanks, Marko |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if worksheet function
Hi Fred,
Thanks for the reply, I think this one is best for my current use. -- Thanks, Marko "Fred Smith" wrote: Try: =if(isnumber(c5),c5,0) Regards, Fred. "supersonikk" wrote in message ... 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) -- Thanks, Marko |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
if worksheet function
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) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "supersonikk" wrote in message ... 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) -- Thanks, Marko |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| worksheet function | Excel Worksheet Functions | |||
| Worksheet Function | Excel Discussion (Misc queries) | |||
| WorkSheet Function Help | Excel Worksheet Functions | |||
| Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions | |||
| Can the offset worksheet function reference another worksheet | Excel Worksheet Functions | |||
Hybrid Mode
