Written by Allen Wyatt (last updated August 30, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365
Georgia has a column of text values extending over thousands of rows. She would like to change the capitalization of the text so that only the first letter of each cell is capitalized.
Assuming that the column, indeed, only contains text values, then you can use the following formula:
=UPPER(LEFT(A1)) & LOWER(MID(A1,2,LEN(A1)-1))
This formula assumes that the text values are in column A. Copy the formula down for as many rows as needed, and you'll have your result. If you simply want to make sure that the first character is uppercase without changing the case of anything else in the cell, then this shorter version will work:
=UPPER(LEFT(A1)) & MID(A1,2,LEN(A1)-1)
If, instead, you want to capitalize the first letter of each word in the cell, then the formula is even simpler:
=PROPER(A1)
These formulas presume that you have a spare column available to house the formula. If you prefer to make your changes in-place, then you'll need to rely on a macro to do the work. Here's an example:
Sub UpperFirst() Dim c As Range Dim t As String For Each c In Selection If Not IsEmpty(c.Value) And VarType(c.Value) = vbString Then t = Trim(c.Value) c.Value = UCase(Left(t, 1) & LCase(Mid(t,2)) End If Next c End Sub
Select the cells you want to affect, then run the macro. It makes sure that the cell contains something and that the something is really a text string. Then, it capitalizes the first letter and makes sure the rest is lowercase. In the process, it also deletes any leading or trailing spaces in the cell.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13952) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
Excel allows you to easily combine text together. Interestingly, it provides two ways you can perform such combinations. ...
Discover MoreTwo rather common trigonometric functions are secants and cosecants. This tip provides information on how to derive ...
Discover MoreThe precision of numeric values you display in a worksheet can be less than what is actually maintained by Excel. This ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-08-30 06:09:56
Barry
Just what I neede thank you.
One small typo but took awhile to find it (still a novice!).
c.Value = UCase(Left(t, 1) & LCase(Mid(t,2))
s/b c.Value = UCase(Left(t, 1)) & LCase(Mid(t,2))
Just an extra bracket before '&': ...(Left(t, 1)) &...
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments