Change lower case to upper case in excel 2007

Change lower case to upper case in excel 2007

In excel there is no easy way to format the lower case text to upper text.

If yo want to change lower case text to upper, you have to use a function =UPPER().
Eg:
Cell A3 -> "this is lower text"
select cell A4 -> =UPPER(A3) - type it in the formula bar
A4 -> "THIS IS LOWER TEXT"

This is not possible to do it for each an every cell, if you have a big document. So writing a macro is a easy way to do that.

Eg:
1. Select View in the main menu of excel 2007
2. Click on Macros (last icon in the right side)
3. Type "change_case" in the Macro name
3. Click on Create button
4. Copy paste the following code (Delete the codes already in the opened window)

Sub change_case()
Dim o As Object
Dim sCap As Integer, _
lCap As Integer, _
I As Integer
Dim testStr As String
Application.ScreenUpdating = False
For Each o In Selection
With o
If Application.IsText(.Value) Then
lCap = .Characters(1, 1).Font.Size
sCap = Int(lCap * 0.85)
'Small caps for everything.
.Font.Size = sCap
.Value = UCase(.Value)
testStr = .Value
'Large caps for 1st letter of words.
testStr = Application.Proper(testStr)
For I = 1 To Len(testStr)
If Mid(testStr, I, 1) = UCase(Mid(testStr, I, 1)) Then
.Characters(I, 1).Font.Size = lCap
End If
Next I
End If
End With
Next o
Application.ScreenUpdating = True
End Sub

5. Go to excel file (click on the 1st icon in the left side of the top tool bar)
6. Select the lower case text in the excel worksheet
7. Again go to Macro (in the View menu last icon of the right side of tool bar)
8. Select the "change_case" in the list of Macro name
9. Click on Run button
10. Save your file.