How do you change the Capitalization in an Excel Workbook?

[Capitalization in Excel]

Word gives userd the ability to change the case of text to UPPER, lower &
Initial Caps...I need to standardize entries - some are lower, some are
upper, I need all entries to be Initial Caps...what do you recommend?

 

[Mike H]

try

=Proper(a1)

 

[Gord Dibben]

You can use helper cells with the function PROPER

i.e. =PROPER(A1) entered in B1

To do a great whack at one go without a helper column you need VBA

Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = Application.Proper(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub

Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = UCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Sub Lower()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = LCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub



Gord Dibben MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

Just to add to Gord's posting...

You would use what Gord posted to fix any **existing** text already on your
worksheet. To make sure any future entries on the worksheet are in proper
case, you will need this worksheet Change event code...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
Target.Value = StrConv(Target.Value, vbProperCase)
Whoops:
Application.EnableEvents = True
End Sub

As written, it applies to the entire worksheet but it can be restricted to
smaller ranges if need be. To install the code, right click the tab of the
worksheet you want this to apply to, select View Code from the popup menu
that appears and copy/paste the above code into the code window that
appeared.

Rick

 

[Mike H]

hi,

And just to add to Ricks' post I wouldn't do this if there are any formula
on your sheet because they will be converted to values.

You could modify it thus

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
If Not Target.HasFormula Then
Target.Value = StrConv(Target.Value, vbProperCase)
End If
Whoops:
Application.EnableEvents = True
End Sub

Mike

 

[Mike H]

I should have said if you enter any NEW formula in your sheet they will be
converted to values.

 

[Rick Rothstein \(MVP - VB\)]

Thanks Mike (I think you have corrected me on this before... I don't know
why I have such a blind spot to it).

Rick

 

[Gord Dibben]

Or to this..........

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
Application.EnableEvents = False
Target.Formula = StrConv(Target.Formula, vbProperCase)
Whoops:
Application.EnableEvents = True
End Sub


Gord