Excel Conditional Formatting - numbers including text

Question

I need some uncommon conditional formatting applied to a column in Excel. They have to be 1 to 4 digit numbers, but if "(tested)" comes after the number, that is acceptable too. Examples of valid values are as follows:

1
10
100
1000
1 (tested)
10 (tested)
100 (tested)
1000 (tested)

Here are examples of invalid values:

10000
10000 (tested)

Since the cells don't have to be completely numerical, I can't make use of a "greater than 9999" rule.

How can I use conditional formatting to highlight these invalid values?

Answer 1

Try this as your formula:

=--Left(A1,find(" ",A1 & " ")-1)<9999