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.

 

[JEC]

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

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

A couple of options depending on your version

+Fluff 1.xlsm
	D	E	F
7	abc	FALSE	FALSE
8	Abc	FALSE	FALSE
9	ABc	TRUE	TRUE
10	ABC	FALSE	FALSE
Main

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

 

[steve the fish]

If you have names with all lowercase then what would McInturff look like?

 

[Jeffrey Mahoney]

A list of names that have two capital letters could be provided to the macro as comparison. This macro is an example

Sub AddExceptions()
  Dim R As Range
  Dim Exceptions() As Variant
  Dim eCnt As Long
  Dim Cel As Range
  Dim Pars() As String
  Dim A As String
  Dim B As String
  Dim X As Long
  Dim Y As Long
  Dim Joined As String
 
  Set R = Sheets("Sheet1").Range("Exceptions")
  'ReDim Exceptions(1 To R.Count)
  Exceptions() = Application.Transpose(R.Value)
  eCnt = UBound(Exceptions())
  Joined = Join(Exceptions, ":")
 
 
  Set R = Range("A2:A200")
  For Each Cel In R
    If Len(Cel.Value) > 0 Then
      Pars() = Split(Cel.Value, " ")
      B = ""
      
      For X = 0 To UBound(Pars())
        A = UCase(Pars(X))
        Pars(X) = WorksheetFunction.Proper(A)
        If InStr(1, UCase(Joined), A) > 0 Then  'Does the name exist in the list of exceptions
          For Y = 1 To eCnt
            If UCase(Exceptions(Y)) = A Then
              Pars(X) = Exceptions(Y)
              Exit For
            End If
          Next Y
        End If
        If B <> "" Then
          B = B & " " & Pars(X)
        Else
          B = B & Pars(X)
        End If
      Next X
      Cel.Value = B
    End If
  Next Cel
 
 
End Sub

 

[Jeffrey Mahoney]

Oh, I created a named range called "Exceptions" on a sheet and filled it with a couple of examples. The named range is dynamic so it can grow easily with new names

 

[steve the fish]

Im still confused how you can have lowercase or uppercase names and expect to recognise a capital I in the middle of a name. Must be missing something.

 

[Jeffrey Mahoney]

Im still confused how you can have lowercase or uppercase names and expect to recognise a capital I in the middle of a name. Must be missing something.

It's impossible to create a algorithm for that, that's why the OP wants to provide a list of possibilities

 

[steve the fish]

Ha thats what i missed! No wonder i was confused.