Basics of VBA / Macros Chapter – 42 (String Manipulation Part-4)

⚡ Macro Developer Toolkit

Copy or Export this Code to VBA

Use these verified developer utilities to run this lesson directly inside your Microsoft Excel Visual Basic Editor.

🖇 View Code Example
⏱ 2 min readUpdated 12 November 2026

Convert to Proper Case

Below we will look at a program in Excel VBA that converts text to proper case. That is, the first letter in each word in uppercase, and all other letters in lowercase.

Situation:

Convert to Proper Case in Excel VBA

We will declare two Range objects. We call the Range objects rng and cell.

Advertisement
Dim rng As Range, cell As Range

Initialise the Range object rng with the selected range.

Set rng = Selection

We want to check each cell in a randomly selected range (this range can be of any size). In Excel VBA, you can use the For Each Next loop for this. Add the following code lines:

For Each cell In rng

 Next cell

Note: rng and cell are randomly chosen here, you can use any names. Remember to refer to these names in the rest of your code.

To ignore a cell that contains a formula, add the following code line between For Each and Next (only if cell.HasFormula is false we continue).

If Not cell.HasFormula Then

 End If

Next, we want to convert each word in this range to ‘proper case’. You can use the worksheet function Proper for this task. Add the following code line in your if statement.

Advertisement
cell.Value = WorksheetFunction.Proper(cell.Value)

Test the program.

Result:

Convert to Proper Case Result

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free Β· AI can be wrong