MoonPoint Support Logo

 


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals



Advanced Search
August
Sun Mon Tue Wed Thu Fri Sat
 
14
     
2022
Months
Aug
Oct Nov Dec


Sun, Aug 14, 2022 10:01 am

Excel Proper Case

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.

Excel Paste Options

Or you can select Paste Special and then click on the same icon that appears beneath Paste Values.

Excel Paste Special

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

[/software/office/excel] permanent link

Once You Know, You Newegg AliExpress by Alibaba.com

Shop Amazon Local - Subscribe to Deals in Your Neighborhood

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo