change case in Excel

[gmac7]

Hello. I've got a column list with a gazzilion words in Excel.
I'd like to change case so that all words begin with capital then lower case.

I'm looking at the "PROPER" function in Excel but it's not working to my likes.

Any help appreciated.

 

[infinitelo]

if all entries are single word you could
=upper(left(a1,1) & lower(mid(a1,len(a1))
or something similar.


if it is to be it's up to me

 

[TonyJollans]

Hi gmac7,

Can you give some examples of where PROPER isn't doing what you want?

Enjoy,
Tony

 

[gmac7]

Not sure, but I've got a column of words and all letters are capitalized. So I just want to highlight the whole column and change the case so the first letters only are capitalized. If this was MSWORD it would be easy.

I no im doing something wrong so please advise.

 

[TonyJollans]

hI gmac7,

You still aren't saying what your problem with PROPER is, so I'll assume it's just that you don't know how to use it to do what you want.

If your capitalised data are in column A, highlight column B and type =PROPER(A1) then press Ctrl-Enter. Then delete column A.

Enjoy,
Tony

 

[TonyJollans]

Hi gmac7,

Oops

I forgot an importanmt step, sorry.

Select Column B
Enter =PROPER(A1)
Press Ctrl-Enter
Select Edit > Copy
Select Edit > Paste Special > Values
Delete Column A

Enjoy,
Tony

 

[PeterMoran]

Hi gmac7,

While Tony has solved your problem here is a macro I use frequently in Excel which does what you initially requested and rotates between the various cases, exactly as what happens when you use Shift+F3 in Word:

Public ShiftCase As Integer

Sub CaseChanger()
'
'Step Through Case Changes
'Created by Peter Moran 6/7/99 (ex PC Mag PC Tech)
'Modified 15/5/03 to exclude cells with formulas
'Note: Including Static below will mean macro holds value
' Static Sub CaseChanger()
'
ShiftCase = ShiftCase + 1
If ShiftCase > 3 Then ShiftCase = 1
For Each Item In Selection
Select Case ShiftCase
Case 1
If Item.HasFormula = False Then
Item.Value = LCase(Item.Value)
End If
Case 2
If Item.HasFormula = False Then
Item.Value = UCase(Item.Value)
End If
Case 3
If Item.HasFormula = False Then
Item.Formula = Application.Proper(Item)
End If
End Select
Next Item
End Sub

Good Luck!
Peter Moran