In this tutorial, we will discuss how to control text input in Google Sheets using data validation to force text entry to all caps, all lowercase, or proper case.

In Google Sheets, Data Validation enables you to restrict or control data entry in various ways. You can use drop-downs, checkboxes, and built-in validation rules. Additionally, you can create your own rules using the custom formula field.

As mentioned above, you can learn here how to use Google Sheets data validation to force text entry to all caps or uppercase, all lowercase, or proper case.

To achieve this, we will be using custom formulas in data validation.

How to Restrict or Make All Text Entry/Input in Capital Letters or Uppercase

You can select either the entire column or specific ranges to enforce all-caps entry.

Steps Involved:

1. Select the range or column where you want to enforce text entry in all caps or uppercase. For example, our range for demonstration purposes here is A1:A15.
2. Go to the ‘Data’ menu and choose ‘Data Validation.’
3. Click on the ‘Add Rule’ button.
4. Under criteria, select ‘Custom formula is.’
5. Enter the following formula: =EXACT(UPPER(A1), A1)
6. Under ‘Advanced Options,’ select either ‘Reject the input’ or ‘Show a warning.’ If you choose the former, you can additionally provide helper text as well.

Formula Explanation:

We use the EXACT function in Google Sheets to compare two strings and determine if they are identical.

Syntax:

EXACT(string1, string2)

UPPER(A1): In this part of the formula, we convert the text in cell A1 to uppercase. The converted value becomes the string1 argument.

A1: This represents the original cell containing the text that you want to check, and it serves as the string2 argument.

The EXACT function then compares the original text in cell A1 (string2) with the same text converted to uppercase (string1). If the original text is already in uppercase, the formula returns TRUE; otherwise, it returns FALSE.

This rule restricts or makes all text entry/input in capital letters in cell A1.

Since we apply this formula to the “Apply to range” A1:A15, the rule applies to each cell within the specified range.

How to Force All Text Input to Small Case | Lower Case | Small Letters

To enforce all text input to be in small or lowercase letters, you can use the following formula in data validation in Google Sheets:

=EXACT(LOWER(A1), A1)

In this formula, replace A1 with the first cell in the ‘Apply to range.’

For example, if you want to force all text input to be in lowercase in the range B10:C20, replace A1 with B10, and the formula will be =EXACT(LOWER(B10), B10).

How to Restrict or Enforce Proper Case for All Text Entries

You have already learned how to restrict or force text entry into All Caps and All Lower Cases. Now, let’s look at how to apply a data validation rule for a Proper Case.

Here’s the custom formula to use within data validation:

=EXACT(PROPER(A1), A1)

Replace both instances of A1 with the cell reference of the first cell in your ‘Apply to range.’

If you attempt to enter anything that violates the rule you’ve set, Google Sheets will display a warning message based on your validation help text. This warning will pop up only if you select “Reject the input” under Advanced Options within the Data Validation dialog box.

Additional Tip: Exclude Specific Text from the Rule

You’ve learned about three different data validation rules that help you restrict text entries to lower, upper, or proper cases.

How can you specify a condition within these rules? For example, you may want to keep most of the text in lowercase but allow some specific exceptions, such as certain abbreviations. How do you specify them?

Here’s an example formula:

=OR(EXACT(LOWER(A1), A1), REGEXMATCH(A1, "^TMI$|^NVM$|^FYI$|^ASAP$"))

The above rule restricts text entries to lowercase letters, except for the abbreviations TMI, NVM, FYI, and ASAP, which can be entered in any case.

To implement this behavior, we’ve used the REGEXMATCH function in combination with an OR logical test. For a better understanding, please refer to the following screenshot.

Related Resources:

1. Change Text Case in Google Sheets: Upper, Lower, Proper, Sentence.
2. Restrict Entering Special Characters in Google Sheets (Data Validation).
3. Distinct Values in Drop-Down List in Google Sheets.