Allow only numeric entries in Excel

[Matt27]

I have an Excel spreadsheet into which Users will enter data. I have a column which will be populated with phone numbers. I want to be able to restrict users to only entering numbers into this column and not allow alphabetical entries (a,b,c, etc.). Is there any way to do this?

 

[abd1311]

select the column
click on Data, then click on Validation
in the Allow, choose whole number, or decimal..
you will have to choose a minimum and a maximum thou.. but no alphabetical entries will be allowed

 

[Matt27]

That's great, thanks for that. Is there any way of doing it the other way round - if I wanted to restrict the users to only entering alphabetical characters into a column and not numbers?

 

[abd1311]

choose custom in the Allow section
and in formula, put =istext(A1)
this will not allow numbers, only texts
but it will allow a mixed between numbers and alphabetical caracters (numbers are usually treated as alpha caracters)



don't forget to give me a star

 

[abd1311]

i found a solution
use the formula isnumber instead of istext, it will give u pure numbers..

 

[KenWright]

Just be aware though that DV is easily bypassed by simply pasting in data, as it blows away the DV.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------