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.

	A	B	C	D	E	F	G
1	Input	Excel formula	UDF				Case Specific Words
2	THE	The	The				a
3	UPS	UPS	UPS				AMA
4	DRIVER	dRiVeR	dRiVeR				an
5	MAY	May	May				at
6	DRIVE	Drive	Drive				dRiVeR
7	IN	in	in				etc
8	A	a	a				FBI
9	TRUCK	Truck	Truck				in
10	OF	of	of				of
11	STEEL	Steel	Steel				PD
12							TX
13							UPS
14							USA
15	THE UPS DRIVER MAY DRIVE IN A TRUCK OF STEEL	The Ups Driver May Drive In A Truck Of Steel	The UPS dRiVeR May Drive in a Truck of Steel

<tbody>
</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