Categories
Excel

Data Validation: Validate The Text Case

Data validation is a feature in Excel that lets you control the type of data that can be entered in a cell or range. You can use data validation to check the text case of the input. For example, you can make sure that the text is uppercase, lowercase, or proper case.

Allow Only Uppercase Text

Follow these steps to allow only uppercase text in a cell/range:

  1. Select the cell or range of cells that you want to validate.
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. In the Data Validation dialog box, on the Settings tab, under Allow, select Custom.
  4. In the Formula box, use the formula =EXACT(A2:A5,UPPER(A2:A5)) to allow only uppercase text. The A2:A5 is the range that you want to validate.
  5. Click the Error Alert tab, enter a Title and Error Message.
  6. Click OK to apply the data validation rule.

Now, if you enter a text that does not match the specified case, you will see an error message and you will not be able to enter the data until you correct it.

Allow Only Lowercase Text

Follow these steps to allow only lowercase text in a cell/range:

  1. Select the cell or range of cells that you want to validate.
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. In the Data Validation dialog box, on the Settings tab, under Allow, select Custom.
  4. In the Formula box, use the formula =EXACT(A2:A5,LOWER(A2:A5)) to allow only uppercase text. The A2:A5 is the range that you want to validate.
  5. Click the Error Alert tab, enter a Title and Error Message.
  6. Click OK to apply the data validation rule.

Now, only lowercase text can be entered in the selected cells. If you try to enter text other than lowercase, you will get the error message you mentioned on the Error Alert tab in the Data Validation dialog box.

Allow Text If the First Character of Every Word is Capitalize

Follow these steps to allow text if the first character of every word is capitalized in a cell/range:

  1. Select the cell or range of cells that you want to validate.
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. In the Data Validation dialog box, on the Settings tab, under Allow, select Custom.
  4. In the Formula box, use the formula =EXACT(A2:A5,PROPER(A2:A5)) to allow only uppercase text. The A2:A5 is the range that you want to validate.
  5. Click the Error Alert tab, enter a Title and Error Message.
  6. Click OK to apply the data validation rule.

Allow Text If Only the First Character is Capitalized

Follow these steps to allow entering text in a cell range only if the first character is capitalized of the text string:

  1. Select the cell or range of cells that you want to validate.
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. In the Data Validation dialog box, on the Settings tab, under Allow, select Custom.
  4. In the Formula box, use the formula =EXACT(A2:A5,UPPER(LEFT(A2:A5,1))&LOWER(RIGHT(A2:A5,LEN(A2:A5)-1))) to allow only uppercase text. The A2:A5 is the range that you want to validate.
  5. Click the Error Alert tab, enter a Title and Error Message.
  6. Click OK to apply the data validation rule.

Now, only text entries that start with a capital letter will be accepted in the selected cells. If you enter a text that does not meet this criterion, you will get an error message.

Data Validation