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, 2022 in Others by Kithuzzz
• 28,520 points
85 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, 2022 by narikkadan
• 52,760 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, 2022 in Others by narikkadan
• 52,760 points
81 views
0 votes
1 answer

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

answered Oct 28, 2022 in Others by narikkadan
• 52,760 points
160 views
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, 2022 in Others by narikkadan
• 52,760 points
62 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, 2022 in Others by narikkadan
• 52,760 points
215 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, 2022 in Others by Edureka
• 13,630 points
213 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, 2022 in Others by Edureka
• 13,630 points
385 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, 2022 in Others by Edureka
• 13,630 points
174 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 22,940 points
160 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, 2022 in Others by narikkadan
• 52,760 points
280 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, 2022 in Others by narikkadan
• 52,760 points
418 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