How to Ensure that Only Text Values are Entered in a Spreadsheet

Manually entering client names into a worksheet can be a time-consuming task and unless you’re a data capturing guru, mistakes can happen. The ISTEXT function in Microsoft® Excel® can help eliminate some of those mistakes. Although it cannot correct any misspelled names, it can ensure that no numeric characters are entered where only text should be captured.

The ISTEXT function will check whether a value is text and returns true or false, true should the value be text, and false if otherwise. Using the ISTEXT function in custom data validation will then prevent any characters besides text to be entered.

Note: Download the sample workbook to practice this exercise

Applies To: Microsoft® Excel® 2007, 2010 and 2013

1. With reference to the sample workbook; highlight the data range A2:A8.
2. Select the Data tab, then Data Validation under the Data tools group.

tip-1

 

 

 

 

3. Select Custom and enter the formula: ISTEXT(A2) under the formula tab.

tip-2

 

 

 

 

 

 

 

4. Under the Input Message tab; enter the respective message.

tip-3

 

 

 

 

 

 

 

5. Select the Error Alert tab and enter the error message.

tip-4

 

 

 

 

 

 

 

6. Select OK.

7. If you enter a number in cell A2; an error message will be displayed.

tip-5

 

 

 

 

 

8. Click Retry and enter a text value ; say Joe Bloggs
9. The text value will be accepted.

When you’re done only text values will be accepted in your data range.