Excel DATA VALIDATION Using ISTEXT, UPPER, EXACT and AND Function

★ 25 minutes read

If you want to learn how to use DATA VALIDATION to allow only uppercase text in Excel, you will have to use the combination of several functions AND, EXACT, UPPER and ISTEXT. DATA VALIDATION. These allow you to validate fields that are entered and changed by certain criteria.

Allow uppercase text only in Excel

In this tutorial, we will cover field validation to allow only uppercase text in cells. Since this requires knowledge of several functions, after this tutorial you will learn how to use ISTEXT, UPPER, EXACT and AND functions. Let’s go through the example in order to understand better DATA VALIDATION for uppercase text only. We want to restrict data entrance and data change in column City (cell range C3:C7) only with uppercase text.

Click on the data tab and under section Data Tools choose Data Validation. In tab Settings, part Allow, choose Custom. To restrict data entrance only to uppercase letters enter the Formula:

=AND(EXACT(C3,UPPER(C3)),ISTEXT(C3))

 

Explanation of formula

First, we will explain the EXACT part of the formula. This formula compares string in the selected cells and returns value TRUE if the cells are identical, and FALSE if not. Please note that EXACT is case sensitive function. Formula syntax looks like:

=EXACT (text1, text2)

We also used the UPPER function in order to compare the entered text with the same uppercase text. This formula is just converting text to the uppercase text string. Formula syntax is simple:

=UPPER (text)

In our example, we used the combination of EXACT and UPPER function, where text 1 is cell C3, and text 2 UPPER(C3) in EXACT formula syntax.  We are comparing entered City, and the same cell converted to uppercase since our criteria are uppercase text only. If City is entered in uppercase, formula result will be TRUE.

=EXACT(C3,UPPER(C3))

In the previous step, we checked if data entered is in uppercase text. We should also check if data entered is text by using ISTEXT function. This is another data validation condition since users can populate cells with numbers, dates,  etc. The syntax for the ISTEXT function looks like:

=ISTEXT(value)

=ISTEXT(C3)

ISTEXT is checking if the entered value is a text string and giving result TRUE if the condition is met and FALSE if the condition is not met.

We need to check both conditions if data entered is text and If data entered is in uppercase text. Because of that, we are using AND function. You should use AND function if there are several conditions that have to be met at the same time. Formula results are Boolean TRUE/FALSE, and formula result will be TRUE only if all conditions in the formula are TRUE.

Formula syntax for AND function looks like:

=AND(logical1,[logical2], …)

In our example, logical 1 is EXACT/UPPER formula combination that is checking if data entered is in uppercase. Logical2 is ISTEXT formula checking if data entered is a text string.

=AND(EXACT(C3,UPPER(C3)),ISTEXT(C3))

We put data validation condition only in cell C3. In order to expand it to column “City” just select relevant cell range and click Data Validation under Data tab, section Data Tools. You will be asked to expand Data Validation to the cell range.

If we try to enter the city in lowercase, the error message will appear as in the example below:

You can change a text in a warning message going to Data Validation, Error Alert tab. In the box Error message enter warning message and choose Style Stop. It is important to use Style Stop since this style will allow you to enter data only with conditions defined in previous steps. If you choose Style Information or Warning, you will be allowed to enter data that does not meet the defined condition.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar