How to check if upper or lower letter cases are included in Excel

Regular expression is useful to find certain chalacters are included in strings. But you must install add-in to use a regular expression in Excel.

However, you can know a cell value includes upper / lower case letters or numeric with Excel standard functions.

How to check if UPPER case letters are included

=IF(EXACT(cell,LOWER(cell)),"FALSE","TRUE")

This function returns TRUE if a cell value has upper letter cases.

The EXACT function compares a original string and a lowered string. If they do not match, you can find that the original string has upper case letters, and then  the function returns TRUE.

How to check if LOWER case letters are included

=IF(EXACT(cell,UPPER(cell)),"FALSE","TRUE")

This function returns TRUE if a cell value has lower letter cases.

How to check if Numerics are included

=IF(COUNT(INDEX(FIND(ROW($1:$10)-1,cell),)),"TRUE","")

This function returns TRUE if a cell value has numerics from 0 to 9.

In the first argument of the FIND function, 0 to 9 are specified with the ROW function. Then the INDEX functions returns a array that has each position of 0 to 9 in a cell value. The COUNT function counts the array length, and if the length is one or more, it means the cell value has numerics.

 

By combining these functions, you can do various checks like “if the cell value has upper case letters, but not numeric”.

Related Posts