Text functions in Excel 2013 are found on the Text command button’s drop-down menu on the Ribbon’s Formulas tab (Alt+MT). There are two types of text 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.
Text functions such as 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.
Follow these steps for using the PROPER function to convert text entries to the proper capitalization:
Position the cell cursor in cell C3 and then click the Text command button on the Ribbon’s Formulas tab (or press Alt+MT) and then choose PROPER from its drop-down menu.
The Function Arguments dialog box for the PROPER function opens with the Text box selected.
Click cell A3 in the worksheet to insert A3 in the Text box of the Function Arguments dialog box and then click OK to insert the PROPER function into cell C3.
Excel closes the Insert Function dialog box and inserts the formula =PROPER(A3) in cell C3, which now contains the proper capitalization of the last name Aiken.
Drag the Fill handle in the lower-right corner of cell C3 to the right to cell D3 and then release the mouse button to copy the formula with the PROPER function to this cell.
Excel now copies the formula =PROPER(B3) to cell D3, which now contains the proper capitalization of the first name, Christopher. Now you’re ready to copy these formulas with the PROPER function down to row 17.
Drag the fill handle in the lower-right corner of cell D3 down to cell D17 and then release the mouse button to copy the formulas with the PROPER function down.
The cell range C3:D17 now contains first and last name text entries with the proper capitalization. Before replacing all the uppercase entries in A3:B17 with these proper entries, you convert them to their calculated values. This action replaces the formulas with the text as though you had typed each name in the worksheet.
With the cell range C3:D17 still selected, click the Copy command button on the Home tab of the Ribbon. Immediately choose the Paste Values option from the Paste command button’s drop-down menu.
You’ve now replaced the formulas with the appropriate text. Now you’re ready to move this range on top of the original range with the all-uppercase entries. This action will replace the uppercase entries with the ones using the proper capitalization.
With the cell range C3:D17 still selected, position the white-cross mouse or Touch pointer on the bottom of the range; when the pointer changes to an arrowhead, drag the cell range until its outline encloses the range A3:B17 and then release the mouse button or remove your finger or stylus from the touchscreen.
Excel displays an alert box asking if you want the program to replace the contents of the destination’s cells.
Click OK in the Alert dialog box to replace the all-uppercase entries with the properly capitalized ones in the destination cells.
Your worksheet now looks like the one shown. Everything is fine in the worksheet with the exception of the two last names, Mcavoy and Mcclinton. You have to manually edit cells A11 and A12 to capitalize the A in McAvoy and the second C in McClinton.