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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
Solution
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’)
 
Upvote 0
A couple of options depending on your version
+Fluff 1.xlsm
DEF
7abcFALSEFALSE
8AbcFALSEFALSE
9ABcTRUETRUE
10ABCFALSEFALSE
Main
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
 
Upvote 0
If you have names with all lowercase then what would McInturff look like?
 
Upvote 0
A list of names that have two capital letters could be provided to the macro as comparison. This macro is an example

VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,697
Members
448,293
Latest member
jin kazuya

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