Restrict or Force Text Entry to All Caps, All Lower Cases, or Proper Case in Google Sheets

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.
Data Validation Rule to Force Text Entry to All Caps

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.

Example to a Validation Error Message in Google Sheets

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.

Data Validation Rule to Restrict Text Entry to Lower Case Except Certain Words

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.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

5 COMMENTS

  1. =AND(EXACT(PROPER(A2:A1000), A2:A1000), A2:A1000=TRIM(A2:A1000))

    UPPER CASE is not allowed. Trailing white space at the end is not allowed. Use Proper Case.

  2. Hi Prashanth,

    Great material! I need to define multiple validation criteria, for example, a column with Proper case validation except in the case where the value is “CLOSED”. I don’t know if using OR or how to solve this.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.