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 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.



Bill Jelen is the author / co-author of:

MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.