Convert to Proper Case in Excel VBA
This page illustrates how to create a program in Excel VBA that converts text to proper case. That is, the first letter of each word is in uppercase, and all other letters are in lowercase.
Situation:
1. First, we declare two Range objects. We call the Range objects rng and cell.
2. We initialize the Range object rng with the selected range.
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:
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).
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.
6. Test the program.
Result:
If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Data Analysis in Excel and Excel VBA.
