Validate Uppercase Entries in Excel

The Data Validation tool in Excel can be used to validate uppercase entries. This post demonstrates two examples of validation to ensure text is entered in uppercase.

The first ensures that the whole entry has been written in uppercase, and the second will only validate the first letter of an entry.

Validate Text Entries to be in Uppercase

This example will ensure that the whole entry is in uppercase.

1. Select the cells you want to validate
2. Click the Data tab on the Ribbon and then click the Data Validation button
3. Click the Allow: list arrow and select Custom

4. Type the following formula in the box provided replacing E2 with the first cell of the range selected

=EXACT(E2,UPPER(E2))

The Exact function compares two text entries and returns True if they are exactly the same, and False if they are different.

The Upper function is used to convert the case of text to uppercase. In this example it is used to compare an uppercase version of the text entry to the actual text entry.

5. Click the Error Alert tab in the dialogue box
6. Enter a customised error message which explains what the user has done wrong, and what they should do

7. Click Ok

Validate only the First Letter of an Entry to be Uppercase

You may wish to validate only the first letter of an entry to be in uppercase. This can be popular with ID’s for products and employees that commonly have a prefix lettering in uppercase.

1. Select the cells you want to validate
2. Click the Data tab on the Ribbon and then click the Data Validation button
3. Click the Allow: list arrow and select Custom

4. Type the following formula in the box provided replacing A2 with the first cell of the range selected

=EXACT(LEFT(A2,1),UPPER(LEFT(A2,1)))

In this formula, the LEFT function is used to extract the first letter from the text entered. This enables the formula to only check the first character.

5. Click the Error Alert tab in the dialogue box
6. Enter an error message that instructs the user what has gone wrong and what they should do
7. Click Ok

Using formulas in Data Validation enables you to create complex validation criteria. This is great because as well as ensuring accurate data entry, it also instructs the spreadsheet users on how they should be entering it.

In my Excel stock control spreadsheet, stock items need to be entered with the first letter  in uppercase. Instead of using Data Validation I used the Ucase function in VBA to convert the entry to uppercase.

This is a good demonstration of other techniques available. Different techniques work better in different scenarios.