Google Sheets – How to Specify Character Range in Conditional Formatting

conditional formattinggoogle sheets

I'm trying to color cells that do not start with a letter; but I'm not sure how to specify a character range in this format.

Cells contents such as:

!this one    <--- formatted
this is fine
@email.com   <--- formatted
 leading space   <--- formatted

Ideally, it would color the cell for any non-letter.

Best Answer

Formula

=REGEXMATCH(A1,"^[^a-zA-Z]")

Explanation

Set conditional formatting to use a custom formula. Apply the above formula.

The first ^ means start of the string
Note that the second ^ is inside of [], this means a logical NOT.
The a-zA-Z are a two character ranges, lowercase letters and uppercase letters.

References

REGEXMATCH (Google Docs editors Help)
Getting started with Regular Expressions (Documentation - Stack Overflow )

Related Solutions

Google Sheets – Conditional Formatting with Multiple Criteria for a Range of Cells

Writing a formula that satisfies your criteria is a matter of breaking down what your criteria are and implementing corresponding Sheets functions.

You have established that the formatting should be applied only if a cell is not empty. The EQ function tests whether or not one value (such as a referenced cell's) is the same as another. Since we want to test against an empty cell, we will use "" (the empty string) in our EQ function. So EQ(A1,""). But you want it to return TRUE if the cell is NOT empty, so we will enclose this expression within the NOT function.

NOT(EQ(A1,""))

You also established that the formatting should be applied if the cell's value is not -------. Once again, we can use EQ for this. EQ(A1,"-------"). And again, we'll wrap it in the NOT function to meet your criterium.

NOT(EQ(A1,"-------"))

Finally, you want BOTH of these criteria to be met if the formatting is to be applied. So we'll use the AND function, inputting the two formulae we put together above as the arguments. Your final formula is:

=AND(NOT(EQ(A1,"")),NOT(EQ(A1,"-------")))

Make sure that, when you are creating your conditional formatting rule, you set the condition field to "Custom formula is," or it won't work.

ADDENDUM: Normal Human has offered an alternate formula that is both shorter and easier on the eyes than mine. It utilises logical operators in place of some of Sheets' logical functions and so is not quite as easy to follow without knowledge of these operators. The logic is exactly the same, however. (<> is the operator for "not equal to.")

=AND(A1<>"", A1<>"-------")

Google Sheets Conditional Formatting – Conditional Format for Column Headers Where All Cells Are Numbers

Apply conditional formatting with custom formula

=arrayformula(sum(N(not(isnumber(A3:A)))))=0

This can be applied even to a range of headers such as A1:E1; the formula, entered as above, will be interpreted in a relative way for other columns.

Explanation

not(isnumber(...)) is true if the entry is not a number
N converts logical true/false to integers 1 or 0
sum adds these.
arrayformula makes sure the entire range is handled
the sum is equal to 0 if all entries are numbers.

Note

Dates and times are considered numbers, because internally, they are. For example, the date 05/26/2016 is the number 42516 formatted as a date.

Best Answer

Formula

Explanation

References

Related Solutions

Google Sheets – Conditional Formatting with Multiple Criteria for a Range of Cells

Google Sheets Conditional Formatting – Conditional Format for Column Headers Where All Cells Are Numbers

Explanation

Note

Related Topic