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

Nate Lawrence

New Member
Joined
Sep 24, 2019
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 
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?
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
@Nate Lawrence
:confused: 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
7Aaron Smith
8AARON Smith
9AARON SMITH
10Ann McInturff
11Ken SiMoNs
12Karl van der Breggen
13Karl Van Der Poel
14KARL VAN DER POEL
15Karl van der PoeL
16Jill aDAMS
Check Caps (7)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
O7:O16Expression=IF(EXACT(O7,UPPER(O7)),,LET(c,MID(O7,SEQUENCE(LEN(O7)),1),FIND(11,CONCAT(IF(c=" "," ",IF(EXACT(c,UPPER(c)),1,""))))))textNO
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,722
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top