Macro to Use "Proper" function on column of all cap text

Posted by Dwight on March 16, 2001 6:56 AM

Trying to automate conversion of long columns of all cap text (usually proper names)to more natural looking 1st letter caps using the "Proper" function. Recorded following macro to be run after selecting a column, but can't figure out how to make it useful for any column instead of the specific column ("A") I used to record the macro.
Can you help?

Selection.Insert Shift:=xlToRight
Selection.FormulaR1C1 = "=PROPER(RC[1])"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Selection.End(xlUp).Select
Range("A1").Select

Posted by KRISTEL K on March 16, 2001 7:09 AM

Selection.Insert Shift:=xlToRight
Selection.FormulaR1C1 = "=PROPER(RC[1])"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Offset(0, 1).Select
Selection.Delete Shift:=xlToLeft
Selection.End(xlUp).Select

Posted by KRISTEL K on March 16, 2001 7:11 AM

sorry, i didn't see that your code was still under mine...

Selection.Insert Shift:=xlToRight
Selection.FormulaR1C1 = "=PROPER(RC[1])"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Offset(0, 1).Select
Selection.Delete Shift:=xlToLeft
Selection.End(xlUp).Select

Posted by Dwight on March 16, 2001 7:21 AM

Loverly! Thanks, Kristel

Works perfectly.

Posted by John Nicholas on March 18, 2001 5:05 AM

To change small case to capitals

I have searched and searched but cannot find a idiot proof answer to the question:
I have several cell in an excel worksheet that are in small case text. I want to change them to Capital letters. I have read about the PROPER function, but I just cannot seem to get anywhere.
Please if anyone can give me a simple solution in detail please, I would be forever grateful

John
johnnicholas@email.com

PS. Could not start a new post for some reason, so jumped in on a similar thread.