Converting worksheet with all caps to normal text

Sub ConvertToUpperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = UCase(Rng.Value)
End If
Next Rng
End Sub

The code

If Rng.HasFormula = False Then

ensure that formulas will not be changed. This is important because when you assign something to the Value property of a range, the formula in that cell is lost, and is replaced by the constant value.

To convert the text to lower case or proper case, change the line

Rng.Value = UCase(Rng.Value)

to

Rng.Value = LCase(Rng.Value)

To convert the text to proper case (proper case is where the first letter of each word is capitalized, and the other letters or lower case -- This Is An Example Of Proper Case), change the line

Rng.Value = UCase(Rng.Value)

to

Rng.Value = StrConv(Rng.Value, vbProperCase)

Susan, you can use the PROPER function to convert the data, select your formula data and then Copy|PasteValues over-writing your original data.

Does this help you?

BarrieBarrie Davidson

Barrie,

I ended up using the following approach and it worked like a charm:

1. Put your cursor in the empty cell next to the cell you want to change
2. Type =proper(cell address)and press enter
3. Try copying down

Eddie,

Thanks for your input. Someone else sent me the following and it worked great:

1. Put your cursor in the empty cell next to the cell you want to change
2. Type =proper(cell address)and press enter
3. Try copying down

Thanks again. Susan