Identifying words w/ more than 1 capital (but less than all caps)?

[Nate Lawrence]

Hello,

I have to check several hundred names per day before our company prints and mails forms to people.

We have a very basic macro which changes text to Formal Case (rather than lowercase or ALL CAPS).
However, it corrupts any name with more than one capital letter (McInturff, for example) by changing any letter except the first to lowercase.

I need a way to highlight all cells which contain a word with more than one capital letter, but less than ALL CAPS.
My thought there is that I can quickly make a list of all the words that I need to switch back to mixed case after running our macro.

A formula to run in Conditional Formatting to highlight all cells which contain a word with multiple capitals in a single word (but ignores all caps words) would be a great boon to me.

--

Even better would be a single formula which changes things to Formal Case, but which doesn't alter words such as DeRevere, but I didn't want to ask for anything too complicated.
For now, some conditional highlighting to catch items for manual review would be fine.

 

[Peter_SSs]

If a formula you previously submitted should be able to handle that, please redirect me to that post.

You could test that as easily s we could.

Having said that, I get both first and last names which contain more than one capital letter and I'd like to have a cell highlighted if any single word in a cell has more than one capital.

What about 8-10 varied examples (preferably with XL2BB) and the expected results taking into account things like all caps, no caps, multi-word surnames like have been discussed during the thread already?

 

[Peter_SSs]

@Nate Lawrence
I'm confused in relation to the original question as I note that you have marked the post #2 formula as the solution despite
a) It returning False for an example like in post #13 (McMahon), and
b) It does not address this requirement that you stated:

.. more than one capital letter, but less than ALL CAPS.

In relation to your latest request ..

cell highlighted if any single word in a cell has more than one capital.

.. but still assuming no highlight if the cell is ALL CAPS, you could try this CF rule
(I wasn't sure whether you would want O8 highlighted or not since the whole cell is not all caps and a word contains multiple caps, but each word is either all caps or only single cap)??

Nate Lawrence.xlsm
	O
7	Aaron Smith
8	AARON Smith
9	AARON SMITH
10	Ann McInturff
11	Ken SiMoNs
12	Karl van der Breggen
13	Karl Van Der Poel
14	KARL VAN DER POEL
15	Karl van der PoeL
16	Jill aDAMS
Check Caps (7)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O7:O16	Expression	=IF(EXACT(O7,UPPER(O7)),,LET(c,MID(O7,SEQUENCE(LEN(O7)),1),FIND(11,CONCAT(IF(c=" "," ",IF(EXACT(c,UPPER(c)),1,""))))))	text	NO