Excel VBA Capitalize String: UCase, StrConv, and More

VBA & macros · Updated November 22, 2026

To capitalize a string in Excel VBA, use the UCase function to convert all letters to uppercase (e.g., UCase("hello") returns “HELLO”), or use StrConv with the vbProperCase argument to capitalize the first letter of each word (e.g., StrConv("hello world", vbProperCase) returns “Hello World”). For capitalizing only the first letter of a string, combine UCase with Left and Mid.

Why Capitalization Matters in VBA

Capitalizing strings is a common data-cleaning task in Excel. You might need to standardize names, addresses, product codes, or user input before storing them in a worksheet or comparing them with other data. VBA provides several built-in functions to handle case conversion, each with different behavior. Understanding these functions helps you write cleaner, more reliable macros that produce consistent output.

The Core Functions: UCase, LCase, and StrConv

VBA offers three primary functions for case conversion:

  • UCase(string) – Converts all letters to uppercase. Example: UCase("Excel") returns “EXCEL”.
  • LCase(string) – Converts all letters to lowercase. Example: LCase("Excel") returns “excel”.
  • StrConv(string, vbProperCase) – Converts the first letter of each word to uppercase and all other letters to lowercase. Example: StrConv("hELLO wORLD", vbProperCase) returns “Hello World”.

These functions work on any string expression, including variables, cell values, or literal text. They are case-insensitive in the sense that they ignore the original casing and apply a uniform rule.

How to Capitalize the First Letter Only

Sometimes you only want the first character of a string capitalized, leaving the rest unchanged. There is no single built-in function for this, but you can combine existing ones:

Function CapitalizeFirst(s As String) As String
    If Len(s) = 0 Then Exit Function
    CapitalizeFirst = UCase(Left(s, 1)) & Mid(s, 2)
End Function

This function takes the first character with Left(s, 1), converts it to uppercase, and concatenates it with the remainder of the string (Mid(s, 2)). For example, CapitalizeFirst("vBA") returns “VBA”.

Using StrConv for Proper Case (Title Case)

The StrConv function with vbProperCase is the most common way to capitalize each word in a string, which is useful for names, cities, or titles. Note that it also lowercases the rest of each word. For example:

Dim city As String
city = StrConv("new york", vbProperCase)
' city now equals "New York"

Be aware that vbProperCase treats any non-letter character as a word separator. So “O’Brien” becomes “O’Brien” (the apostrophe splits the word, capitalizing the ‘B’). If you need to handle such cases specially, you may need a custom function.

Applying Capitalization to a Range of Cells

To capitalize all cells in a range, loop through the cells and assign the converted value. Here is an example that converts column A to proper case:

Sub CapitalizeColumnA()
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("A1:A10") ' adjust as needed
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            cell.Value = StrConv(cell.Value, vbProperCase)
        End If
    Next cell
End Sub

This macro checks for empty cells to avoid errors. Always save a copy of your workbook before running a macro, as changes cannot be undone with Ctrl+Z.

Common Problems and Misconceptions

One common mistake is assuming UCase affects only the first letter. It actually uppercases the entire string. Another misconception is that StrConv with vbProperCase will preserve existing capitalization; it does not—it forces a specific pattern. Also, note that StrConv is locale-aware; it may behave differently on systems with different language settings, particularly for characters like accented letters.

Another frequent issue is trying to use worksheet functions like PROPER inside VBA without the Application.WorksheetFunction prefix. You can use Application.WorksheetFunction.Proper as an alternative to StrConv, but it has similar limitations.

Comparing VBA Capitalization with Excel Worksheet Functions

Excel’s worksheet functions UPPER, LOWER, and PROPER do the same basic jobs as UCase, LCase, and StrConv(...vbProperCase). However, there are differences:

  • PROPER in Excel capitalizes the first letter of each word and lowercases the rest, just like StrConv.
  • Worksheet functions can be used directly in cells without VBA, but VBA functions are faster when processing many cells in a loop.
  • In VBA, you can call worksheet functions via Application.WorksheetFunction.Proper, but StrConv is native and often more efficient.

For a one-off conversion, you might use a worksheet formula. For automating a task, VBA is more flexible.

Performance and Best Practices

When capitalizing large ranges, avoid writing to individual cells one by one if possible. Instead, read the range into an array, modify the array, and write it back in one operation. This can be significantly faster. Here is an example:

Sub CapitalizeRangeFast()
    Dim dataArray As Variant
    Dim i As Long, j As Long
    dataArray = Range("A1:C100").Value
    For i = 1 To UBound(dataArray, 1)
        For j = 1 To UBound(dataArray, 2)
            If Not IsEmpty(dataArray(i, j)) Then
                dataArray(i, j) = StrConv(dataArray(i, j), vbProperCase)
            End If
        Next j
    Next i
    Range("A1:C100").Value = dataArray
End Sub

This reduces the number of interactions between VBA and the worksheet, improving performance.

Frequently Asked Questions

How do I capitalize the first letter of every word in a cell using VBA?

Use StrConv(cell.Value, vbProperCase). For example, StrConv("hello world", vbProperCase) returns “Hello World”. Assign the result back to the cell.

Can I use the Excel PROPER function inside VBA?

Yes, you can use Application.WorksheetFunction.Proper(string). It works similarly to StrConv with vbProperCase, but note that PROPER may treat certain characters differently; for most text, the results are identical.

Why does StrConv capitalize the letter after an apostrophe?

Because vbProperCase treats any non-letter character as a word separator. So in “O’Brien”, the apostrophe splits the word, and the ‘B’ is capitalized. If you need to avoid that, you would need a custom function that handles apostrophes specially.

Is there a way to capitalize only the first letter of a string and leave the rest unchanged?

Yes, use the custom function shown above: UCase(Left(s, 1)) & Mid(s, 2). This does not change the rest of the string.

Does UCase work with non-English characters?

Yes, UCase and LCase work with accented characters in most cases, but the behavior may depend on the system’s locale. For example, UCase("é") typically returns “É”. For full Unicode support, consider using the StrConv function with the appropriate locale ID.

Can I undo a macro that changes cell values?

No, macros cannot be undone with Ctrl+Z. Always save a backup copy of your workbook before running any macro that modifies data.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.