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, JR in A2, I
then had Aikin, Jr in 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 B at 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 A for 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".
