MrExcel.com - Your One Stop for Excel Tips & Solutions

Change All Caps to Proper Text

May 01, 2002 - by

Ruth asks:

I need to convert an Excel file into Salesforce.com. The file was given to me in all capital letters. How do I change the case globally so that it is properly formatted with Ruth (capital first letter and the rest in small case) so that I do not have to go in and manually retype everything? (500+ lines)?

The =PROPER() function will do this for most records.

Let's say your data is in A2:G600

Over in blank columns off to the right, enter a formula of =PROPER(A2)

Copy this formula from, say H2 to H2:N600. Make the shape of this range of formulas match the shape of your original range. If you original data has 7 columns, make this data have 7 columns.

You then have to change the formulas to values using these steps:

  • Highlight your range of formulas - H2:N600
  • Edit > Copy
  • Edit > Paste Special > Click Values > Click OK

Note

You need to carefully look through the results. Excel does not handle McDonald correctly - it will set it to Mcdonald. Check the "Mc" and "Van" records and manually correct these.