Changing the Case of Text

CW-NEN

Board Regular
Joined
Jul 18, 2007
Messages
55
Is there a way of changing the case of text. I have been copying my data into word, using the change case function and then copying it back. I am afraid that something will go wrong and would like to keep my data in excel.

Quick addition: I am trying to get Title Case going. but going all upper of lower would not be so bad.

Thanks
Chris
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

CW-NEN

Board Regular
Joined
Jul 18, 2007
Messages
55
Quick addition: I am trying to get Title Case going. but going all upper of lower would not be so bad.
 

bryhamm

Board Regular
Joined
Jun 3, 2004
Messages
115
=proper(A1) will capitalize just the first letter of each word

=upper(A1) will capitalize everything

=lower(A1) will give lower case for everything
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
=PROPER(a1) will capitilise first letter

=upper(A1) All upper case

=lower(A1) All lower case

You can also force text to a certain case on entry using VBE let me know if you would like to do this.
 

CW-NEN

Board Regular
Joined
Jul 18, 2007
Messages
55
Ugg I am making a mess out of this thread.

Title would be best. All upper would not be so bad.
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
edit**

To force all Proper case (Capitlise first letter)

right click sheet tab name and click view code:

Paste this code in:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next
        Application.EnableEvents = False
        Target = StrConv(Target, vbProperCase)
        Application.EnableEvents = True
    On Error GoTo 0
    
End Sub
close the visual basic editor....type something in lower case press enter text will change to proper case

This does the same but forces upper case:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next
    	Application.EnableEvents = False
        Target = UCase(Target)
        Application.EnableEvents = True
    On Error GoTo 0
    
End Sub
 

CW-NEN

Board Regular
Joined
Jul 18, 2007
Messages
55
Word calls it "title case." When the first letter of each word is capitalized.
 

Forum statistics

Threads
1,181,657
Messages
5,931,254
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top