How to capitalize the first word in a string (or the first letter in a cell) using an Excel macro instead of an Excel function or formula

Although you can use a Microsoft Excel function or formula to capitalize the first word in a string (or the first letter in a cell), there are times when you may want to change the case of the text in the same cell the data was entered, programmatically. Just follow these steps:

1. Hit [ALT]+[F11] to open the Visual Basic Editor

2. Hit [ALT]+[I]+[M], or just click “Insert” > “Module”

3. In the pop-up window copy the ff. code”:

Sub CapsFirstLetter()
Dim Cell As Variant
For Each Cell In Selection
Cell.Formula = UCase(Left(Cell.Text, 1)) & LCase(Right(Cell.Text, Len(Cell.Text) - 1))
End Sub

4. Hit [ALT]+[Q], or just click “File” > “Close and return to Microsoft Excel”

5. Run the Macro in Excel by going to:

Microsoft 2007: View > Macro > Macro and then Run “CapsFirstLetter”

Microsoft 2010: Tools > Macros > View Macros and then Run “CapsFirstLetter”.

This will capitalize all the text in the range you selected on the worksheet.


Want to capitalize the first character in every word using an Excel macro? View this tutorial »

This entry was posted in Macros. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

Post a Comment

Your email is never published nor shared. Required fields are marked *

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>