Is it possible to adjust the capitalization of a large amount of data at one time?

[mimih23]

Hello!
I have a spreadsheet with over thousands of names and addresses and would like to format the way the information is listed. Here is an example:

John DOE
JOHN DOE
john doe
john Doe

I would like to be able to format them so that they each appear as such:

John Doe

Is that possible without having to manually update each record???? Any help would be greatly appreciated!! Thanks!!

 

[scottylad2]

Excel Workbook
	D	E
34	John DOE	John Doe
35	JOHN DOE	John Doe
36	john doe	John Doe
37	john Doe	John Doe
Sheet1

 

[Andrew Poulsom]

There's a macro here:

http://www.cpearson.com/Excel/ChangingCase.aspx

 

[UniMord]

I have a slightly different macro, similar to Chip Pearson's (as cited above by Andrew Poulsom), but my version will cycle through the various cases on each call.

Sub Caps()
'Toggle case of selection.
    Dim curSel As Range, r as Range 
    Static curCap%, prevSel As Range
 
    On Error Resume Next
    Set curSel = Intersect(Selection, Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
    If curSel Is Nothing Then Exit Sub
 
    'Errors if curSel not same or subset of prevSel.
    'Fine to ignore, since curSel.Count <> Nothing (took care of that above).
    curCap = IIf(curSel.count <> Intersect(prevSel, curSel).count, 0, (curCap + 1) Mod 3)
    On Error GoTo 0
 
    For Each r In curSel
        Select Case curCap
            Case 0: r.Value = UCase(r.Value)
            Case 1: r.Value = StrConv(r.Value, vbProperCase)
            Case 2: r.Value = LCase(r.Value)
        End Select
    Next
 
    Set prevSel = curSel
End Sub

 

[mimih23]

Oh my gosh you guys are awesome!! Will these macros work on addresses or cells in general filled with several amounts of text:

Example:
123 ambrook st. 123 Ambrook St.
123 AMBROOK st 123 Ambrook St.

Thoughts?

Thanks!

 

[mimih23]

Oh I tried the =Proper() and that worked on the addresses. Yeah! I love this forum!

Thanks everyone!

 

[scottylad2]

That will work on all text within cells as per the below sample

Excel Workbook
	B
6	That will work on all text within cells
7	That Will Work On All Text Within Cells
Sheet3

 

[Atroxell]

PROPER() does a great job, but you still have to look through your data to make sure the function designates the upper and lower cases correctly. I work with a lot of names and addresses and have found many instances where proper did exactly what it was asked to do--but it left a few things behind that were not ideal.

"O'BRIEN" will properly capitalize to "O'Brien" , but "OBRIEN" (no apostrophe) will become "Obrien". And watch for names like "MACMURRAY" that should be "MacMurray". Same holds true with surnames with "Mc" at the start.

Similar issues arise when you have "PO BOX", "P.O. BOX", "P O BOX" and street names like "37TH Street", which becomes "37Th Street" when it should be "37th Street".

HTH

 

[mimih23]

That is good to know Atroxell! Thanks!!