Text detector

  • Thread starter Thread starter O2 andy
  • Start date Start date
O

O2 andy

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
 
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
 
Back
Top