Table of Contents

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

1. First we declare two Range objects. We call the Range objects rng and cell.

Dim rng As Range cell As Range

2. We initialize the Range object rng with the selected range.

Set rng = Selection

3. 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.

4. 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

5. 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.

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

6. Test the program.

Result:

Convert to Proper Case Result

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog