Properly Capitalize Excel Text With “Proper”
Today I was working on a huge Microsoft Excel spreadsheet that contained a column of law firm names. Most of these names were in all caps, instead of only capitalizing the first letter of each word and leaving the rest lowercase. I wanted them properly capitalized, so I struck out to find an easy way to do it.
Proper() to the rescue! Simply put, proper() converts text to its proper capitalization. All I had to do was something like “=proper(a1)” and the firm’s name was automatically converted to only having the first letter of each word capitalized. The only problem is that if A1 contains something that you want to keep all caps (such as PC, LLP, or III for a person’s name). The simple solution here is to use the proper() function and then do a quick search & replace for those values that should remain all caps.
In the end, proper() allowed me to convert 140+ firm names to proper capitalization in about a 30 seconds, instead of the 15 minutes it would have taken by manually re-typing them all. That’s a time saver, right?
[tags]legal andrew, microsoft excel, excel, proper, capitalization, caps[/tags]
Get more legal tips
|
See also... |
Comments
10 Responses to “Properly Capitalize Excel Text With “Proper””
-
Legal tips by email
Sign-up below to get email tips and exclusive discounts on videos, webinars, and future items.
All fields are required.
-
Receive updates
By email
By rss (full feed)
-
About Andrew Flusche
Lawyer, bicyclist, husband.
More about me...
Tumble Log
-
Popular Posts
October 16th, 2006
Yep, that is cool
October 16th, 2006
Yoav,
I’m glad you liked the tip. Hopefully it will be useful for you in the future.
Take care,
Andrew
January 8th, 2007
kick ass
April 7th, 2007
Great tip! Just what I was looking for too.
April 7th, 2007
Andrew,
I’m glad the Excel tip helped you out. It’s amazing how something so simple can be elusive.
Thanks for commenting,
Andrew
October 4th, 2007
Thanks for the tip. 🙂
January 9th, 2008
this will really help with future mailings, but i can’t manipulate my newly-transformed data. oh well, it’s still good to know!
michael
January 9th, 2008
michael – I hope the tip does help you out!
June 19th, 2009
The proper() function works as desired, but the new cell contains the formula…How can I replace the caps in, say, (A1) in the original column so the data can be manipulated?
June 19th, 2009
@Doug – The only way I know to do that is to manually copy and paste the data to another column. But I believe you’ll have to “paste special” and do the value of the formula only.