Convert to Proper Case with Exceptions

[Capt_Ron]

I have found plenty of code to convert text in a range from UPPERCASE to Proper Case but I want to be able to exclude from the conversion any text that matches text found in a list on another worksheet (USA, TX, PD, UPS, a, an, at, in , on, etc.).

Can this be done referencing the list as a named range or would it be best to simply refer to the list of words by Sheet.Range?

Anyone have an example I could follow?

Ron

 

[MikeRRC]

This approach uses a single list of non-proper case words.
The column of Case Sprecific Words is a named range Words (=Sheet1!$G$2:$G$14).
The words in this range can have characters of mixed cases.
This range needs to be sorted ascending.

For single words, the spreadsheet formula (in B2) works:
=IF(ISNUMBER(MATCH(A2,Words,0)),LOOKUP(A2,Words,Words),PROPER(A2))

If you have cells with more than one word, the UDF CorrectCase works
=CorrectCase(A2,Words) is in C2

If needed, more punctuation can be added to the array in WordsOf.

Function CorrectCase(ByVal inputString As String, ByVal caseListRange As Range) As String
    Dim arrWords As Variant
    Dim i As Long, Pointer As Long
    On Error GoTo Halt
    Pointer = 1
    CorrectCase = inputString
    arrWords = WordsOf(inputString)
    
    For i = 0 To UBound(arrWords)
        Pointer = InStr(Pointer, inputString, arrWords(i))
        Mid(CorrectCase, Pointer) = CorrectCaseOneWord(CStr(arrWords(i)), caseListRange)
    Next i
    
Halt:
    On Error GoTo 0
End Function

Function WordsOf(inputString As String) As Variant
    Dim Delimiters As Variant, aDelimiter As Variant
    Dim arrResult As Variant
    
    Delimiters = Array(" ", ",", ".", ";", ":", Chr(34), vbCr, vbLf): Rem add to as needed
    
    For Each aDelimiter In Delimiters
        inputString = Application.Substitute(inputString, aDelimiter, Delimiters(0))
    Next aDelimiter
    
    arrResult = Split(inputString, CStr(Delimiters(0)))
    WordsOf = arrResult
End Function

Function CorrectCaseOneWord(inWord As String, caseListRange As Range) As String
    With caseListRange
        If IsError(Application.Match(inWord, .Cells, 0)) Then
            CorrectCaseOneWord = Application.Proper(inWord)
        Else
            CorrectCaseOneWord = Application.Lookup(inWord, .Cells)
        End If
    End With
End Function

In the below sheet,
The Excel formula in B2 and down is
=IF(ISNUMBER(MATCH(A2,Words,0)),LOOKUP(A2,Words,Words),PROPER(A2))

The UDF is in C2 downwards,
=CorrectCase(A2,Words)

Words is a named range for G2:G14.
Note that Words is sorted ascending.

<tbody>
[TD="width: 125"] A [/TD]
[TD="width: 125"] B [/TD]
[TD="width: 125"] C [/TD]
[TD="width: 25"] D [/TD]
[TD="width: 25"] E [/TD]
[TD="width: 25"] F [/TD]
[TD="width: 25"] G [/TD]

[TD="bgcolor: #A0A0A0, align: center"] 1 [/TD]
[TD="bgcolor: #FFFFFF, align: left"] Input [/TD]
[TD="bgcolor: #FFFFFF, align: left"] Excel formula [/TD]
[TD="bgcolor: #FFFFFF, align: left"] UDF [/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]Case Specific Words[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 2 [/TD]
[TD="bgcolor: #FFFFFF, align: left"]THE[/TD]
[TD="bgcolor: #FFFFFF, align: left"]The[/TD]
[TD="bgcolor: #FFFFFF, align: left"]The[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]a[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 3 [/TD]
[TD="bgcolor: #FFFFFF, align: left"]UPS[/TD]
[TD="bgcolor: #FFFFFF, align: left"]UPS[/TD]
[TD="bgcolor: #FFFFFF, align: left"]UPS[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]AMA[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 4 [/TD]
[TD="bgcolor: #FFFFFF, align: left"]DRIVER[/TD]
[TD="bgcolor: #FFFFFF, align: left"]dRiVeR[/TD]
[TD="bgcolor: #FFFFFF, align: left"]dRiVeR[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]an[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 5 [/TD]
[TD="bgcolor: #FFFFFF, align: left"]MAY[/TD]
[TD="bgcolor: #FFFFFF, align: left"]May[/TD]
[TD="bgcolor: #FFFFFF, align: left"]May[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]at[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 6 [/TD]
[TD="bgcolor: #FFFFFF, align: left"]DRIVE[/TD]
[TD="bgcolor: #FFFFFF, align: left"]Drive[/TD]
[TD="bgcolor: #FFFFFF, align: left"]Drive[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]dRiVeR[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 7 [/TD]
[TD="bgcolor: #FFFFFF, align: left"]IN[/TD]
[TD="bgcolor: #FFFFFF, align: left"]in[/TD]
[TD="bgcolor: #FFFFFF, align: left"]in[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]etc[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 8 [/TD]
[TD="bgcolor: #FFFFFF, align: left"]A[/TD]
[TD="bgcolor: #FFFFFF, align: left"]a[/TD]
[TD="bgcolor: #FFFFFF, align: left"]a[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]FBI[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 9 [/TD]
[TD="bgcolor: #FFFFFF, align: left"]TRUCK[/TD]
[TD="bgcolor: #FFFFFF, align: left"]Truck[/TD]
[TD="bgcolor: #FFFFFF, align: left"]Truck[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]in[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 10 [/TD]
[TD="bgcolor: #FFFFFF, align: left"]OF[/TD]
[TD="bgcolor: #FFFFFF, align: left"]of[/TD]
[TD="bgcolor: #FFFFFF, align: left"]of[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]of[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 11 [/TD]
[TD="bgcolor: #FFFFFF, align: left"]STEEL[/TD]
[TD="bgcolor: #FFFFFF, align: left"]Steel[/TD]
[TD="bgcolor: #FFFFFF, align: left"]Steel[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]PD[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 12 [/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]TX[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 13 [/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]UPS[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 14 [/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"]USA[/TD]

[TD="bgcolor: #A0A0A0, align: center"] 15 [/TD]
[TD="bgcolor: #FFFFFF, align: left"]THE UPS DRIVER
MAY DRIVE IN
A TRUCK OF STEEL[/TD]
[TD="bgcolor: #FFFFFF, align: left"]The Ups Driver
May Drive In
A Truck Of Steel[/TD]
[TD="bgcolor: #FFFFFF, align: left"]The UPS dRiVeR
May Drive in
a Truck of Steel[/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]
[TD="bgcolor: #FFFFFF, align: left"][/TD]

</tbody>

Mike, this doesn't work for me.

If I want to change "GT 6# SAFFLOWER BAG" to "GT 6# Safflower Bag", I add GT to the case specific words list. I also have AWG included in that list, and the formula is grabbing AWG instead of GT, so the output is "AWG6# Safflower Bag". Why is it grabbing AWG instead of GT?

Thanks