Text detector
 

I'm stuck on creating a formula. Here's what i'm trying to do.

In a certain field an entry should be of numerical value only, no text, not
dots no dashes. What i'm looking to do is return a value of 0 for anything
that has anything other than a number in it.

Is this possible?

Thanks

Andy

Text detector
 

Andy,

There are two problems - dashes are often used in date entry, so it looks
like a string with dashes, but it is really a number that Excel formats to
look like a date with dashes. The other is the 'dots' are decimal points in
some systems, and they are allowed in numbers.

I'm going to assume you want integer values only, so try and use Data /
Validation.... Whole number only, and set a max and min value. You can also
set the error message and the entry prompt message to help your users.

If you really need a formula, something along the lines of

=IF(ISERROR(INT(A1)),0,IF(A1<INT(A1),0,A1))

HTH,
Bernie
MS Excel MVP

"O2 andy" wrote in message
...
I'm stuck on creating a formula. Here's what i'm trying to do.

In a certain field an entry should be of numerical value only, no text,
not
dots no dashes. What i'm looking to do is return a value of 0 for
anything
that has anything other than a number in it.

Is this possible?

Thanks

Andy

Text detector
 

Maybe something like this: =IF(ISTEXT(A1),0, ...
--
Max
Singapore
http://savefile.com/projects/236895
Files: 352, Subscribers: 53, Downloads: 15,500
xdemechanik
---
"O2 andy" wrote:
I'm stuck on creating a formula. Here's what i'm trying to do.

In a certain field an entry should be of numerical value only, no text, not
dots no dashes. What i'm looking to do is return a value of 0 for anything
that has anything other than a number in it.

Is this possible?

Thanks

Andy

Text detector
 

Hi Bernie,

Formula works a treat, thanks!!!

Andy

"Bernie Deitrick" wrote:

Andy,

There are two problems - dashes are often used in date entry, so it looks
like a string with dashes, but it is really a number that Excel formats to
look like a date with dashes. The other is the 'dots' are decimal points in
some systems, and they are allowed in numbers.

I'm going to assume you want integer values only, so try and use Data /
Validation.... Whole number only, and set a max and min value. You can also
set the error message and the entry prompt message to help your users.

If you really need a formula, something along the lines of

=IF(ISERROR(INT(A1)),0,IF(A1<INT(A1),0,A1))

HTH,
Bernie
MS Excel MVP

"O2 andy" wrote in message
...
I'm stuck on creating a formula. Here's what i'm trying to do.

In a certain field an entry should be of numerical value only, no text,
not
dots no dashes. What i'm looking to do is return a value of 0 for
anything
that has anything other than a number in it.

Is this possible?

Thanks

Andy