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

0 votes
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 in Others by Kithuzzz
• 20,660 points
26 views

1 answer to this question.

0 votes

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.

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

answered Nov 21 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Convert word document to excel

See the solution here: https://superuser.com/questions/747197/how-do-i-copy-word-tables-into-excel-without-splitting-cells-into-multiple-rows In essence, I had ...READ MORE

answered Oct 23 in Others by narikkadan
• 37,660 points
35 views
0 votes
1 answer
0 votes
1 answer

MAX function in Excel: is it possible to provide the range by means of variables?

Try this: =MAX(INDEX(A:A,B2):INDEX(A:A,B3)) READ MORE

answered Nov 15 in Others by narikkadan
• 37,660 points
25 views
0 votes
1 answer

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

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

answered Nov 20 in Others by narikkadan
• 37,660 points
52 views
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
142 views
0 votes
1 answer

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

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

answered Feb 16 in Others by Edureka
• 13,640 points
240 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17 in Others by Edureka
• 13,640 points
109 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18 in Others by gaurav
• 22,040 points
71 views
0 votes
1 answer

Convert Excel and Word files to PDF Using ruby

 You can combine some: For excel files - ...READ MORE

answered Sep 26 in Others by narikkadan
• 37,660 points
135 views
0 votes
1 answer

Way to overcome Excel Vlookup function limit of 256 characters

If you are using VLOOKUP like this: =VLOOKUP(A2,D2:Z10,3,FALSE) i.e. ...READ MORE

answered Sep 30 in Others by narikkadan
• 37,660 points
141 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP