CODEWITHSUNDEEP
excelexcel-formula
Denis Trunin
Denis Trunin

Reputation: 3

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

I am searching for the formula that converts first letter of each word to uppercase(and nothing more). Any idea how to do this? for example: "my WAA task" should be converted to "My WAA Task" I found PROPER function, but it also converts all others letters to lower case(so it gives me "My Waa Task") but I don't need this

Upvotes: 0

Views: 948

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Two methods,

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 enter.

enter image description here

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)

enter image description here

Upvotes: 1

Related Questions