VBA - uppercase of the first letter in each word, while the rest remains intact

[georgiboy]

Here is a revised function, you could convert it to a sub if you needed to:

Function FixString(rCell As Range)    
    Dim var As Variant, too As Variant, Exclusion As Boolean
    
    Application.Volatile
    too = Split("with,a,of", ",")
    var = Split(rCell, " ")
    
    For x = LBound(var) To UBound(var)
        Exclusion = False

        For y = LBound(too) To UBound(too)
            If LCase(too(y)) = Application.Trim(LCase(var(x))) Then
                var(x) = too(y)
                Exclusion = True
                Exit For
            End If
        Next y
        
        If Exclusion = False Then
            var(x) = UCase(Left(var(x), 1)) & Right(var(x), Len(var(x)) - 1)
        End If
        
        If Application.Trim(LCase(var(x))) = "dot" Then var(x) = " "
    Next x
    
    FixString = Application.Trim(Join(var))
    
End Function

Hope this helps