-4

I'm trying to make error messages appear when someone tries to enter the wrong data type in a certain cell with data validation in Excel 2010. Is there a feature or some sort of formula I can use?

Ex. There is an error when someone tries to enter their name as a number, or id as a word.

7
  • Please provide a few examples of what is allowed and what is not allowed. How can someone enter their name as a number? That does not make sense. Provide some real examples. What are the columns? What is the expected input?
    – teylyn
    Apr 22, 2014 at 4:46
  • 2
    How can you add your name as a number? Let's say your name is Alex. How would you enter your name Alex as a number? That just does not make sense. Your question does not make sense. Don't abuse the people who are trying to help you. Reflect and get your premises right. You need to define your outcomes a lot more clearly. "Enter names as a number" does not cut it. What do you need? Allow only alpha character? Allow only numeric characters? Allow letters and numbers but not symbols like +_)(*&^%$$# -- be specific. As it stands right now, nobody knows what you really need. Define it.
    – teylyn
    Apr 22, 2014 at 12:02
  • You aren't reading the question right at all.
    – Alex
    Apr 22, 2014 at 16:39
  • I explained to you already, stop with the needless commenting.
    – Alex
    Apr 22, 2014 at 21:09
  • 1
    Good luck with your question. Some further explanation would help, but it's up to you.
    – teylyn
    Apr 22, 2014 at 21:13

2 Answers 2

2

To allow text only select the Data tab from the ribbon and choose Data Validation and again select Data Validation. Change the Allow drop down to Custom and put this in the formula field:

=ISTEXT(A2:A14)

Put whatever cell range is relavant to you in the parenthesis.

You can also create a custom error alert.

To allow only numbers, repeat the steps above but select Whole Numer or Decimal from the allow drop down menu.

3
  • I suppose that would work but the name values I am using are entered by the user. Although I think if you adjusted that formula to require to use letters of the alphabet would that work?
    – Alex
    Apr 22, 2014 at 3:57
  • Yes, you need to determine the rules, select the cells you wish and apply those rules (following the steps above). Then whoever is using this Excel file will be prompted with an error message if they deviate from said rules. Apr 22, 2014 at 5:51
  • I did as you said but it's still allowing numbers to be entered.
    – Alex
    Apr 22, 2014 at 16:42
0

You could use something like

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))<=LEN(A1) 

This will indicate if a number is being used in the cell.

Source

Not the answer you're looking for? Browse other questions tagged .