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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel Data Analysis and Business Modeling today!
US ZIP Codes can be of two varieties: five-digits or nine-digits. Here's how to convert longer ZIP Codes to the shorter ...
Discover MoreWant to add up a bunch of scores, without including the lowest one in the bunch? You can make a small change to your ...
Discover MoreIf you need to add dashes between letters and numbers in a string, the work can quickly get tedious. This tip examines ...
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