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:
The result of the PROPER() function on these is wrong: :
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.
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.