One of the very common functions that we are used to with MS office product is the change case function which will allow us to easily change the case of the text string we have. Excel is missing that option so here is a code snipet that you can use for the task.

Sub Proper_case() 'Each words first letter is capitalized
Dim selected_range As Range
Set selected_range = Range(Selection.Address)

Dim old_value As String
For Each cell In selected_range
    old_value = vbNullString: old_value = cell.Value
    cell.Value = WorksheetFunction.Proper(old_value)
End Sub

Based on the range that you select, after you run this piece of code it will change the cell’s contents to PROPER CASE. Copy paste the code above into a new module in the VBA editor and save it. When you are back in Excel main window press alt+F8 which is the shortuct to show the codes in the VBA section and run the one called Proper_case.