MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Change All Caps to Proper Text

May 01, 2002 - by Bill Jelen

Ruth asks:

I need to convert an Excel file into 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


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.

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.