How to convert text to proper case in an excel sheet

I was doing a form letter mailing here at work and when we run queries against our database, the result set is pulled into an Excel spreadsheet in all upper case. So, a customer's address would look like this:

JOHN SMITH
1313 MOCKINGBIRD LN
TRANSYLVANIA, PA 11111

Obviously, in a professional letter to a customer addressing them by their full name in upper case just clashes with a letter that is properly puncuated and capitalized. What I needed to do was convert it to this:

John Smith
1313 Mockingbird Ln
Transylvania, PA 11111


Each field is laid out separately in the Excel sheet, so the data was a bit easier to work with. To put it simply the customer's first name, last name, address, city, state, and zip code were all in their own column. I couldn't quite find a simple VBA routine on Google that would do this for me so I wrote my own.

The code below will run against the text that is actively selected in Excel.



'--------------------------Begin Code--------------------------------'
Option Explicit
Private Sub ProperCase()
Dim thisCell As Range
Dim strSoFar As String
Dim strTmp As String
Dim x As Long
Dim tmpArr() As String

For Each thisCell In Selection.Cells
tmpArr = Split(thisCell, " ")
For x = 0 To UBound(tmpArr)
If x = 0 Then
thisCell = UCase(Mid(tmpArr(x), 1, 1)) & LCase(Mid(tmpArr(x), 2, Len(tmpArr(x))))
Else
thisCell = thisCell & " " & UCase(Mid(tmpArr(x), 1, 1)) & LCase(Mid(tmpArr(x), 2, Len(tmpArr(x))))
End If
Next x
Next thisCell
End Sub
'--------------------------End Code--------------------------------'


When run the code will take all of the text in each cell and separate it where this a space. It will then capitalize the first letter of each word and lower case the rest of it. Then each word is rejoined with a space. If you have double spaces in your text it will probably add an extra space or two.

So, anything that looks like jOHN sMITH or JOHN SMITH or john smith will end up looking like John Smith after the macro is run.