I inherited an Excel spreadsheet containing names and addresses for all the members of an organization where all the letters for the names and addresses were capitalized. I wanted to convert the names and addresses to "proper case" where only the first letters of names, streets, and cities are capitalized (in the Python programming language proper case is known as "title case"). Fortunately, Excel provides a function, propercase, to perform that function. To perform the conversion, I inserted a new column to the right of each of the columns where all uppercase letters were used. The first column in the Excel workbook contained the last names for the members. There was a header titled "Last Name" in cell A1 with the first person's last name appearing in cell A2, so in the newly inserted column, B, I put
=proper(A2)in cell B2. When I hit Enter, instead of
AIKIN, JRin A2, I then had
Aikin, Jrin B2. I then clicked in cell B2 and dragged the mouse pointer downwards to the last row containing a member's contact information, which resulted in all the members last names being placed in column B with the proper case, i.e., uppercase letters for the first letter of the name and lowercase letters for the rest of the name. I couldn't just delete column A then, which would cause a
#REF!message to appear in column B for every name, since I was using a formula that depended on a value being in column A. So, instead, I copied all of column B by clicking on the
Bat the top of the column to highlight all entries in the column. I then used the Ctrl-C keys to copy all of the entries. I then needed to paste the values in column B into column A, rather than pasting in the formulas. You can do so, by right-clicking on the column designator letter, i.e.the
Afor column A in this case, and then choosing the appropriate paste option, To paste just the values and not the formulas and formatting, you can click on the icon of a clipboard with "123" on it.
Or you can select Paste Special and then click on the same icon that appears beneath Paste Values.
Once you've pasted the values that are in proper case, you can delete the column, e.g., B in this case, where the formulas are used.
Note: for addresses with Post Office boxes, any instance of "PO BOX" would be replaced with "Po Box," but you can use Excel's find and replace feature to search for all instances of "Po Box" and have it replace all those occurences with "PO Box".