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.

 

[Nate Lawrence]

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.

 

[Fluff]

Thanks for updating your profile. Do any of the solutions do what you wanted.

 

[Peter_SSs]

This is a formula which indicates "TRUE" if a word has more than 1 capital letter.

=SUM(--ISNUMBER(FIND(CHAR(ROW($65:$90)),A1)))>1

Does it?

21 10 05.xlsm
	A	B
1	McMahon	FALSE
Check Caps

Cell Formulas
Range	Formula
B1	B1	=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
	D	E	F
7	McMahon	FALSE	FALSE
8	Abc	FALSE	FALSE
9	ABc	TRUE	TRUE
10	ABC	FALSE	FALSE
Check Caps (2)

Cell Formulas
Range	Formula
E7:E10	E7	=AND(SUM(--ISNUMBER(FIND(CHAR(ROW($65:$90)),D7)))>1,SUM(--ISNUMBER(FIND(CHAR(ROW($65:$90)),D7)))<LEN(D7))
F7:F10	F7	=LET(Tot,SUM(--ISNUMBER(FIND(CHAR(ROW($65:$90)),D7))),AND(Tot>1,Tot<LEN(D7)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D7:D10	Expression	=LET(Tot,SUM(--ISNUMBER(FIND(CHAR(ROW($65:$90)),D7))),AND(Tot>1,Tot<LEN(D7)))	text	NO

My attempt

21 10 05.xlsm
	D
7	McMahon
8	Abc
9	ABc
10	ABC
11	AARON
12	aaron
13	McInturff
14	SiMoNs
Check Caps (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D7:D14	Expression	=LET(c,MID(D7,SEQUENCE(LEN(D7)),1),t,CONCAT(IF(EXACT(c,UPPER(c)),1,"")),AND(LEN(t)>1,LEN(t)<LEN(D7)))	text	NO

 

[Fluff]

Good point Peter.
Another attempt

+Fluff 1.xlsm
	D
7	AAron
8	Abc
9	ABc
10	ABC
11	AARON
12	aaron
13	McInturff
14	SiMoNs
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D7:D14	Expression	=LET(Cde,CODE(MID(D7,SEQUENCE(LEN(D7)),1)),Tot,SUM(IF((Cde>=65)*(Cde<=90),1,0)),AND(Tot>1,Tot<LEN(D7)))	text	NO

 

[Peter_SSs]

Another attempt

Developing that idea a bit further ..

21 10 05.xlsm
	D
7	McMahon
8	Abc
9	ABc
10	ABC
11	AARON
12	aaron
13	McInturff
14	SiMoNs
Check Caps (4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D7:D14	Expression	=LET(s,SUM(--(ABS(77.5-CODE(MID(D7,SEQUENCE(LEN(D7)),1)))<13)),(s>1)*(s<LEN(D7)))	text	NO

 

[Fluff]

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
7	AAron
8	Abc
9	ABc
10	ABC
11	AARON
12	aaron
13	McInturff
14	SiMoNs
15	van der Breggen
16	van der Poel
17	van der poel
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D7:D17	Expression	=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))))	text	NO

 

[Peter_SSs]

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.

 

[Fluff]

You're quite right, forgot to account for the spaces. It should be

=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," ","")))))

 

[JEC]

What about this one, looking at the first letter aswell

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW($65:$90)),"")))>1

 

[Nate Lawrence]

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.