About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store


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

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.

By Bill Jelen on 01-May-2002 Consulting can be hired to implement this concept, or many other cool applications, with your data. provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.


Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.