How do I change the case of my text to proper?

[Guest]

My entire spreadsheet is in all caps but I need it to be proper. Example:

This is how it looks now:

KERRY THOMAS
123 ANY STREET
ANYTOWN, GA 12344

I need it to be:

Kerry Thomas
123 Any Street
Anytown, GA 12344

 

[BenjieLop]

The syntax is

=proper(Cell_Number)

Hope this helps you.

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/case.htm

 

[Bob Phillips]

in an adjacent cell

=PROPER(A1)

and copy down

 

[Trevor Shuttleworth]

Try this:

Sub ConvertCase()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Intersect(Cells, ActiveSheet.UsedRange)
If Not cell.HasFormula Then
cell.Value = WorksheetFunction.Proper(cell.Value)
End If
Next
Application.ScreenUpdating = True
End Sub

Regards

Trevor

 

[Gord Dibben]

In an adjacent column enter

=PROPER(A1)

Assuming the text is in column A, of course.

Drag/copy down.

When happy with results, select the column and Copy>Paste Special>Values over
the original column.

Delete the formula column.

Gord Dibben Excel MVP

 

[kkknie]

You will have an issue when it comes to the state abbreviations since G
will turn into Ga. A way around it is to find/replace on the state nam
(assuming a leading comma and space. Here's the code:

Sub GetProper()

Dim r As Range
Dim strState(59) As String
Dim i As Integer

For Each r In ActiveSheet.UsedRange
r.Value = Application.WorksheetFunction.Proper(r.Value)
Next

strState(1) = "AL"
strState(2) = "AK"
strState(3) = "AS"
strState(4) = "AZ"
strState(5) = "AR"
strState(6) = "CA"
strState(7) = "CO"
strState(8) = "CT"
strState(9) = "DE"
strState(10) = "DC"
strState(11) = "FM"
strState(12) = "FL"
strState(13) = "GA"
strState(14) = "GU"
strState(15) = "HI"
strState(16) = "ID"
strState(17) = "IL"
strState(18) = "IN"
strState(19) = "IA"
strState(20) = "KS"
strState(21) = "KY"
strState(22) = "LA"
strState(23) = "ME"
strState(24) = "MH"
strState(25) = "MD"
strState(26) = "MA"
strState(27) = "MI"
strState(28) = "MN"
strState(29) = "MS"
strState(30) = "MO"
strState(31) = "MT"
strState(32) = "NE"
strState(33) = "NV"
strState(34) = "NH"
strState(35) = "NJ"
strState(36) = "NM"
strState(37) = "NY"
strState(38) = "NC"
strState(39) = "ND"
strState(40) = "MP"
strState(41) = "OH"
strState(42) = "OK"
strState(43) = "OR"
strState(44) = "PW"
strState(45) = "PA"
strState(46) = "PR"
strState(47) = "RI"
strState(48) = "SC"
strState(49) = "SD"
strState(50) = "TN"
strState(51) = "TX"
strState(52) = "UT"
strState(53) = "VT"
strState(54) = "VI"
strState(55) = "VA"
strState(56) = "WA"
strState(57) = "WV"
strState(58) = "WI"
strState(59) = "WY"

For i = 1 To 59
Cells.Replace What:=", " & strState(i), Replacement:=", "
strState(i), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next

End Sub

Got the states fro
http://www.usps.com/ncsc/lookups/usps_abbreviations.html and i
includes things like Virgin Islands etc.

 

[Guest]

This requires using VBA code. If you want to get the job done without
understanding it:

Press Alt+F11 to get into the editor. Then press Control+G to get into the
immediate window. Paste the line below into the window and press Enter.

for each cell in activesheet.usedrange : cell.formula =
strconv(cell.formula, vbProperCase) : next

However, I highly recommend the link to Chip Pearson's site for detail.

 

[Gord Dibben]

Good point about the GA turning to Ga.

The rest of us missed that.

Also thanks for the code.

Gord Dibben Excel MVP