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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
You can use some of Excel's worksheet functions across a range or worksheets, but not all of them. One that has problems ...
Discover MoreWhen you recalculate a worksheet, you can determine the maximum of a range of values. Over time, as those values change, ...
Discover MoreIt's easy to use filtering to hide rows based on the value in a cell, but how do you hide rows based on the values in two ...
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