Fixing Capitalization with Excel 2007's UPPER, LOWER, and PROPER Functions
Excel 2007 includes several Text functions (such as UPPER, LOWER, PROPER, VALUE, TEXT, and DOLLAR) that enable you to perform operations on text entries in a worksheet. These functions are located in the Text category on the Formulas tab of the Ribbon and in the Insert Function dialog box.
Text functions include two types of functions: functions such as VALUE, TEXT, and DOLLAR that convert numeric text entries into numbers and numeric entries into text, and functions such as UPPER, LOWER, and PROPER that manipulate the strings of text themselves.
Many times, you need to use the text functions when you work with data from other programs. For example, suppose that you purchase a target client list on disk, only to discover that all the information has been entered in all uppercase letters. In order to use this data with your word processor’s Mail Merge feature, you would use Excel’s PROPER function to convert the entries so that only the initial letter of each word is in uppercase.
Text functions like the UPPER, LOWER, and PROPER functions all take a single text argument that indicates the text that should be manipulated.
The UPPER function converts all letters in the text argument to uppercase.
The LOWER function converts all letters in the text argument to lowercase.
The PROPER function capitalizes the first letter of each word as well as any other letters in the text argument that don’t follow another letter, and changes all other letters in the text argument to lowercase.
For example, the worksheet in the figure below would be a good candidate for using the PROPER function to convert the uppercase names to the proper case. To do so, you would enter the following function in cell C3:
This function would display the result Aiken in cell C3. Then you would use the Fill handle to copy the function down the range and to the next column, so that all items in columns A and B are converted to the proper case.