Formula to detect CAPITALS in a given cell?

[wildmage]

Hi all,

I have a sheet with many rows and columns of data. Some cells have text in PROPER case and others are all CAPITALS. I need a formula that results in a Y if the referenced cell contains ALL CAPITALS (may include special characters and numbers too - still want a Y). I also want to apply conditional formatting to those cells to highlight them in red.


Any ideas?

 

[tusharm]

Since the FIND function is case sensitive use =FIND(UPPER(A1),A1) where A1 is the cell of interest. If A1 contains all caps it will return 1 else it will return #VALUE! Wrap the FIND() within a IF to get what you want. Use the same technique for the conditional formatting.

 

[PaddyD]

or, for the sake of variety:

=exact(a1,upper(a1))