How to get proper case in Excel with small French words, such as "sur" NOT "Sur" "de" NOT "De"

Question

I am trying to correct the case of first letters of French proper names in multiple calls of Excel sheets using VBA. I have data entered by many different people, some in lowercase, some in upper case, some mixed, etc etc.

I am looking for a better function than the PROPER() function. Though using PROPER() in the formula of a cell gives the correct result for most names (by setting the first letter in words to uppercase and the rest to lowercase) it gives the wrong results when names contain prepositions.

In French, simple names normally start with uppercase, but prepositions usually with lowercase. This is what I want to achieve.

Examples of correct French case of proper names:

1. Jean de Villeneuve
2. Domaine de la Romanée-Conti
3. Cellier des Rois

The result of the PROPER() function on these is wrong: :

1. Jean De Villeneuve
2. Domaine De La Romanée-Conti
3. Cellier Des Rois

Are there any easy ways to use VBA to apply the correct French case to proper names?

If possible, I would like the very first letter of a cell to be uppercase for all words.

Answer 1

You need a custom function for this, because as you noted PROPER only capitalizes the first letter each word in a given string.

Add a standard module (e.g. Module1) to your VBA project, and implement a user-defined function in it:

Option Explicit

Public Function ProperFR(ByVal Value As String) As String

    ' let's start with an array of all words in the input value.
    Dim words As Variant
    ' we use the VBA.Strings.Split function to split the string at each space.
    words = Split(Value, " ")

    ' now let's identify all words we do not want to capitalize:
    Dim nocaps() As Variant
    nocaps = Array("le", "la", "les", "du", "de", "des", "sur")

    ' now we can iterate each word; we know how many there are so we use a For loop.
    Dim i As Long
    For i = LBound(words) To UBound(words) ' never assume array lower/upper bounds

        ' let's copy the current word into a local variable:
        Dim word As String
        word = words(i)

        If i > LBound(words) Then ' always capitalize the first word
            If ArrayContains(nocaps, word) Then
                ' lowercase word
                word = LCase$(word)
            Else
                word = Application.WorksheetFunction.Proper(word)
            End If
        Else
            ' Proper function works fine otherwise
            word = Application.WorksheetFunction.Proper(word)
        End If
        
        ' store the cased word back into the array:
        words(i) = word
        
    Next

    ' assemble the words array back into a string, and return it:
    ProperFR = Join(words, " ")

End Function

Private Function ArrayContains(ByRef Values As Variant, ByVal Value As String) As Boolean
    Debug.Assert IsArray(Values)
    Dim i As Long
    For i = LBound(Values) To UBound(Values)
        ' we use StrComp for an explicit case-insensitive match:
        If StrComp(Values(i), Value, vbTextCompare) = 0 Then
            ArrayContains = True
            Exit Function ' no need to keep looping
        End If
    Next
End Function

And now you can do =ProperFR("Domaine De La Romanée-Conti") and get Domaine de la Romanée-Conti as an output.