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 you have names with all lowercase then what would McInturff look like?
Apologies for my lack of clarity.

If the user input their surname for both their legal form and their shipping address and either lowercased or ALL CAPSed it both times - that's on them.
They'll just get transformed to Formal Case.

What I was trying to address was people who had correctly capitalized their name and our naive Formal Case macro was making worse instead of better.
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Thanks for updating your profile. Do any of the solutions do what you wanted.
 
Upvote 0
This is a formula which indicates "TRUE" if a word has more than 1 capital letter.

Excel Formula:
=SUM(--ISNUMBER(FIND(CHAR(ROW($65:$90)),A1)))>1
Does it?
21 10 05.xlsm
AB
1McMahonFALSE
Check Caps
Cell Formulas
RangeFormula
B1B1=SUM(--ISNUMBER(FIND(CHAR(ROW($64:$89)),A1)))>1


A couple of options depending on your version
Same problem if it is the same letter repeated.
21 10 05.xlsm
DEF
7McMahonFALSEFALSE
8AbcFALSEFALSE
9ABcTRUETRUE
10ABCFALSEFALSE
Check Caps (2)
Cell Formulas
RangeFormula
E7:E10E7=AND(SUM(--ISNUMBER(FIND(CHAR(ROW($65:$90)),D7)))>1,SUM(--ISNUMBER(FIND(CHAR(ROW($65:$90)),D7)))<LEN(D7))
F7:F10F7=LET(Tot,SUM(--ISNUMBER(FIND(CHAR(ROW($65:$90)),D7))),AND(Tot>1,Tot<LEN(D7)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D7:D10Expression=LET(Tot,SUM(--ISNUMBER(FIND(CHAR(ROW($65:$90)),D7))),AND(Tot>1,Tot<LEN(D7)))textNO


My attempt

21 10 05.xlsm
D
7McMahon
8Abc
9ABc
10ABC
11AARON
12aaron
13McInturff
14SiMoNs
Check Caps (3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D7:D14Expression=LET(c,MID(D7,SEQUENCE(LEN(D7)),1),t,CONCAT(IF(EXACT(c,UPPER(c)),1,"")),AND(LEN(t)>1,LEN(t)<LEN(D7)))textNO
 
Last edited:
Upvote 0
Good point Peter.
Another attempt
+Fluff 1.xlsm
D
7AAron
8Abc
9ABc
10ABC
11AARON
12aaron
13McInturff
14SiMoNs
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D7:D14Expression=LET(Cde,CODE(MID(D7,SEQUENCE(LEN(D7)),1)),Tot,SUM(IF((Cde>=65)*(Cde<=90),1,0)),AND(Tot>1,Tot<LEN(D7)))textNO
 
Upvote 0
Another attempt
Developing that idea a bit further ..

21 10 05.xlsm
D
7McMahon
8Abc
9ABc
10ABC
11AARON
12aaron
13McInturff
14SiMoNs
Check Caps (4)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D7:D14Expression=LET(s,SUM(--(ABS(77.5-CODE(MID(D7,SEQUENCE(LEN(D7)),1)))<13)),(s>1)*(s<LEN(D7)))textNO
 
Upvote 0
Just for the fun of it, another option if you can names where the 1st letter in not meant to be capitalised.
+Fluff 1.xlsm
D
7AAron
8Abc
9ABc
10ABC
11AARON
12aaron
13McInturff
14SiMoNs
15van der Breggen
16van der Poel
17van der poel
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D7:D17Expression=LET(Cde,CODE(MID(D7,SEQUENCE(LEN(D7)),1)),Tot,SUM(IF((Cde>=65)*(Cde<=90),1,0)),OR(AND(EXACT(UPPER(LEFT(D7)),LEFT(D7))=FALSE,Tot=1),AND(Tot>1,Tot<LEN(D7))))textNO
 
Upvote 0
Just for the fun of it, another option if you can names where the 1st letter in not meant to be capitalised.
.. but that would highlight VAN DER POEL, which it shouldn't, by my understanding. I think that you'd have to eliminate the spaces and adjust the length or something similar.
I think I'll wait for some more OP feedback before continuing down that path. :)
 
Upvote 0
You're quite right, forgot to account for the spaces. It should be
Excel Formula:
=LET(Cde,CODE(MID(D7,SEQUENCE(LEN(D7)),1)),Tot,SUM(IF((Cde>=65)*(Cde<=90),1,0)),OR(AND(NOT(EXACT(UPPER(LEFT(D7)),LEFT(D7))),Tot=1),AND(Tot>1,Tot<LEN(SUBSTITUTE(D7," ","")))))
 
Upvote 0
What about this one, looking at the first letter aswell

Excel Formula:
=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW($65:$90)),"")))>1
 
Upvote 0
Hello, friends,

I was getting back to this thread today and trying some of your formulas.

One huge fact that I completely neglected to mention in the original post is that our website collects first and last name together in a single field.

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.

There's quite a few formulas in here now.

I'm having trouble applying the conditional formatting formulas correctly, so I suppose that a cell formula is best until I can determine whether it's flagging rows which meet the criteria of any single word in a cell having more than one capital.

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

Forum statistics

Threads
1,212,934
Messages
6,110,762
Members
448,295
Latest member
Uzair Tahir Khan

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