24 November, 2008

Convert text fields in Excel to Proper case

Excel provides an easy formula to set text values in cells to the correct or proper case. This is very helpful when cleaning up data before entering into a database. If the source was from a third-party -- perhaps from user registrations, it's not unusual to have users not capitalize proper names.

Here's an example:
  • You want st. paul to read St. Paul
  • You want project manager to read Project Manager
Here's the formula:
  • =PROPER(A1) where A1 is the cell of the value you want to convert
If you are going to reference the value later, remember you can use the Text command too.
  • =TEXT(PROPER(A1),"#")
If you want to convert the entire value to upper case, use the UPPER function.
  • =UPPER(A1)

No comments: