How to capitalize first letter only or first letter of each word in Excel?
Supposing you have a list of text strings which words are in all uppercase, lowercase or mixed with lowercase and uppercase, in this situation, you need to make the first letter of each word in the cell to be uppercase, and others are lowercase as following screenshot shown. Do you have any good ways to capitalize or uppercase the first letter of each word for multiple cells quickly and easily in Excel?
Capitalize first letter of each word within cell with formula and VBA code
Capitalize first letter only within cell with formula
Capitalize first letter of each word or first letter only with Kutools for Excel
Capitalize first letter of each word within cell with formula and VBA code
To make the first letter of each word within a cell capitalize, you can use the Proper function or a simple VBA code. Please do with following steps:
Method1: Proper function:
1. Enter this formula: =PROPER(A2) into a blank cell next to your data, B2 for instance, see screenshot:
2. And then drag the fill handle down to the cells that you want to apply this formula, and you can see all the first letters of each word within the cell have been capitalized. See screenshot:
Method2: VBA code:
The following VBA code also can help you to uppercase all of the first letter of each word in a cell.
1. Please hold down ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Capitalize first letter of each word in a cell
Sub Proper_Case()
'Updateby20150428
Dim x As Range
Dim Workx As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set Workx = Application.Selection
Set Workx = Application.InputBox("Range", xTitleId, Workx.Address, Type:=8)
For Each x In Workx
x.Value = Application.Proper(x.Value)
Next
End Sub
3. Then press F5 key to execute this code, and select the data range that you want to use in the popped out dialog box, see screenshot:
4. And then click OK button, all the first letter of each word in the cells have become capitalized.
Capitalize the first letter only or the first letter of each word in text string cells
With the help of Kutools for Excel’s Change Case utility, you can quickly change the text strings to upper case, lower case, proper case, sentence case and so on. Click to download Kutools for Excel!
Capitalize first letter only within cell with formula
Sometimes, you just want to uppercase the first letter only and lowercase others in the cell, are you suffered with this problem in Excel? In fact, here is a formula may do you a favor to solve this task.
1. Type this formula into a blank cell besides your data, =UPPER(LEFT(A2,1))&LOWER(RIGHT(A2,LEN(A2)-1)), see screenshot:
2. Then drag the fill handle to the range of cells which you want to contain this formula, and only the first letter of the cells has become uppercase. See screenshot:
Capitalize first letter of each word or first letter only with Kutools for Excel
If you have Kutools for Excel, with its Change Case feature, you can quickly capitalize both first letter of each word and first letter only in a cell.
After downloading and installing Kutools for Excel, please do as follows:
1. Select the data range that you want to use.
2. Click Kutools > Text > Change Case, see screenshot:
3. In the Change Case dialog box, if you select Proper Case, the first letter of each word will be capitalized, see screenshot:
If you choose Sentence case, only the first letter of the text strings are capitalized as following screenshot shown:
Download and free trial Kutools for Excel Now !
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in