Changing the Case of Text

[CW-NEN]

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

 

[CW-NEN]

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

 

[bryhamm]

how do you want to change the case?

 

[bryhamm]

=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]

=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]

Ugg I am making a mess out of this thread.

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

 

[ADAMC]

edit**

To force all Proper case (Capitlise first letter)

right click sheet tab name and click view code:

Paste this code in:

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:

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]

Thanks Guys

 

[bryhamm]

Ugg I am making a mess out of this thread.

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

What exactly do you mean by this?

 

[CW-NEN]

Word calls it "title case." When the first letter of each word is capitalized.