Macro for Ms Excel to Change Word Case

To change case in Ms Excel can be done using upper(), lower() and proper() functions of excel.

But sometime some data getting mix up between upper case and lower case, however if we assign those function to each data that we want to change will time consuming.

Here , I use simple macro to solve to problems. The idea is to insert a new column next to the cell we want to change, then we change the words and copy back to the original column and delete the extra column. I use keyboard shortcut such as CTRL+Shift+U to change to upper case and CTRL+Shift+L to Lower case and CTRL+Shift+P to proper case.

 

Here the macro listing

Sub RubahUpper()

'

' RubahUpper Macro

' Merubah isi sel menjadi huruf besar semua

' Change to Upper case

' Keyboard Shortcut: Ctrl+Shift+U

'

Selection.EntireColumn.Insert

ActiveCell.FormulaR1C1 = "=UPPER(RC[1])"

ActiveCell.Select

Selection.Copy

ActiveCell.Offset(0, 1).Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlToLeft

ActiveCell.Select

End Sub

Sub RubahLower()

'

' RubahLower Macro

' Merubah isi sel menjadi huruf kecil semua

' Change to Lower Case

' Keyboard Shortcut: Ctrl+Shift+L

'

Selection.EntireColumn.Insert

ActiveCell.FormulaR1C1 = "=Lower(RC[1])"

ActiveCell.Select

Selection.Copy

ActiveCell.Offset(0, 1).Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlToLeft

ActiveCell.Select

End Sub

 

Sub RubahProper()

'

' RubahProper Macro

' Merubah isi sel menjadi huruf Besar awal kata

' Change to Proper Case

' Keyboard Shortcut: Ctrl+Shift+P

'

Selection.EntireColumn.Insert

ActiveCell.FormulaR1C1 = "=Lower(RC[1])"

ActiveCell.Select

Selection.Copy

ActiveCell.Offset(0, 1).Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select

Application.CutCopyMode = False

Selection.Delete Shift:=xlToLeft

ActiveCell.Select

End Sub