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.
Nov 21, 2022 in Others 315 views

## 1 answer to this question.

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)
``` • 63,180 points

## Creating a function in excel VBA to calculate the average point in a circular set of numbers

I used the following code to determine ...READ MORE

## Convert Word doc, docx and Excel xls, xlsx to PDF with PHP

After receiving a request, I'll put the ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE