Find consecutive uppercase letters in Excel column

Question

I would like some help with highlighting the cells from a column that have consecutive uppercase letters (2 or more); Many times I see typos and it is very time consuming to manually verify every row.

The column can have values like:

"CEar downing sun"

"Main tWO street"

"Yesterday"

"Today will be bETTer"

In these cases I would like the formula to highlight row 1, 2 and 4. I tried with Visual Basic but I am not too good at it.

I appreciate any help I can get on this.

Answer 1

If you're interested in a non-VBA solution, you could use a formula-based Conditional Formatting rule, viz (applied to cell A1):

=COUNT(1/(MMULT(0+(ABS(77.5-CODE(MID(A1&"?",COLUMN(A:B)-1+ROW(INDIRECT("1:"&LEN(A1))),1)))<13),ROW(1:2)^0)>1))>0

Obviously pay attention to the usual behaviour of relative referencing when setting up this rule.

Regards

Answer 2

I came up with this alternative:-

=SUM(
(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),1))>=64)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),1))<=90)
*(CODE(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))>=64)*(CODE(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))<=90)
)

If used as a stand-alone formula, it will give an error on strings shorter than 2 characters (and must be entered as an array formula), but if used in conditional formatting should work OK.