Using "proper"

[ehughes]

I have a huge list of business names, all in caps. I figured out ho
to use the "Proper" function to convert an individual cell t
upper-lower case, but I don't know how to apply the syntax for th
whole column. I tried "Paste Special" - formulas; it doesn't do th
trick
Help would be greatly appreciated. Thanks

 

[David McRitchie]

Hi Eva,
To apply to the entire column without using a helper column
you would need a macro. And this one is a very good one
to start with using macros. see
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm

Simply using the worksheet function =PROPER(A1)
and the fill handle besides creating lots of extra work
is not going to help you with names like IBM, MacHugh

You will have to do some manual corrections, but you
won't be working with formulas and you quickly learn where
to look for exceptions, which you won't always be able to
get perfect, since some would use different capitalizations for
the same name.

 

[Paul B]

ehughes, here is one way using a macro

Sub Proper_Case()
'select the range you want to change
'and run this macro
Application.ScreenUpdating = False
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = Application.WorksheetFunction.Proper(Rng.Value)
End If
Next Rng
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003
** remove news from my email address to reply by email **

 

[Frank Kabel]

Hi
if your data is in column A you can use the following formula in B1
=PROPER(A1)

Now copy this formula down

 

[BenjieLop]

I had this situation once before. Asuming the first entry on the lis
was on Cell A1, I just entered

=proper(A1)

on Cell B1 and copied down.

There might be a better formula for this but this one worked for me an
I never had to bother with it again.

 

[RonCo]

ehughes, send me a pm, I may have a spreadsheet I can email you tha
might hel

Cheer
Ro

 

[Bender99]

:

 

[ehughes]

Thanks to everybody. The copy a1 into b1 and copy down worked like a
champ. However, I will learn macros as well.