Excel - function to convert first letter of each word to uppercase

Question

Finding a formula that capitalizes the initial letter of each word is something I'm looking for (and nothing more). Any ideas on how to proceed? Changing "my WAA task" to "My WAA Task," for instance I discovered the PROPER function, but it also lowercases all other letters, giving me "My Waa Task," which is not what I need.

Answer 1

there are two methods to do this:

Method 1

Formula, only works with Office 365 or higher:

=TEXTJOIN(" ",,UPPER(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),(ROW($A$1:INDEX($A:$A,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*99+1,99)),1)) & MID(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),(ROW($A$1:INDEX($A:$A,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*99+1,99)),2,99))

This is an array formula and must be confirmed with Ctrl-Shift-enter instead of the enter key.

---

UDF can be used with older versions.

Function fLetter(str As String) As String
    Dim strarr() As String
    strarr = Split(str)

    Dim i As Long
    For i = LBound(strarr) To UBound(strarr)
        strarr(i) = UCase$(Left$(strarr(i), 1)) & Mid$(strarr(i), 2)
    Next i
    fLetter = Join(strarr, " ")
End Function

Then call:

=fLetter(A1)