Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Allow only numeric entries in Excel

Status
Not open for further replies.

Matt27

MIS
Jun 2, 2004
59
GB
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?
 
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
 
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?
 
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 ;)
 
i found a solution
use the formula isnumber instead of istext, it will give u pure numbers..
 
Just be aware though that DV is easily bypassed by simply pasting in data, as it blows away the DV.

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

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top