Hi, if you want free updates from my blog, you can enter your email address here.

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

Sign-up below to receive my bi-monthly email newsletter with free legal tips. All fields are required.





See also...

Comments

10 Responses to “Properly Capitalize Excel Text With “Proper””

  1. Yoav Ezer
    October 16th, 2006

    Yep, that is cool

  2. Andrew F
    October 16th, 2006

    Yoav,

    I’m glad you liked the tip. Hopefully it will be useful for you in the future.

    Take care,
    Andrew

  3. ads
    January 8th, 2007

    kick ass

  4. Andrew K
    April 7th, 2007

    Great tip! Just what I was looking for too.

  5. Andrew Flusche
    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

  6. Tim, UK
    October 4th, 2007

    Thanks for the tip. :)

  7. michael in NE
    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

  8. Andrew Flusche
    January 9th, 2008

    michael – I hope the tip does help you out!

  9. Doug Simmons
    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?

  10. Andrew Flusche
    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.

Comments are automatically closed on older posts.

  • 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
    View Andrew Flusche's profile on LinkedIn
    Andrew Flusche's Facebook Profile
  • Popular Posts