Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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

Read time: 25 minutes

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. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
Yes hello Im looking for help on conditional formatting I currently have a conditional format for D9>D32 and so on from D9:S20. I need to add an if statement that references cell D4 if the first four characters are 1358 for the rule to apply can you please assist?
Solved by Z. U. in 20 mins
I have a list or people who are ranked in numbers from 3 to 6 I need to recognize the contents of each multiple cells and create an equivalent letter. for that value into another column. e.g. Cell E1 = 3 to show in new cell that row (H1) the letter "C" I have multiple rows with different values in column "E" Can this be done?
Solved by I. J. in 30 mins
Use the Vlookup Function to complete the "employee" column of table 2. Use "job Id" from table 2 as your lookup_value(s) and table 1 as your reference.
Solved by C. H. in 16 mins
If a cell in another sheet is populated I need a vlookup done. If the cell is not populated I need the cell to return blank.
Solved by T. D. in 60 mins
I am trying to make a chart that turns a week range red if nothing is entered in the range. If something is entered then I would like it to turn green. Please Help
Solved by E. U. in 43 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc