MoonPoint Support Logo

 


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



Advanced Search
May
Sun Mon Tue Wed Thu Fri Sat
       
18
2008
Months
May


Sun, May 18, 2008 8:33 pm

Capitalizing the First Letter of Names with Excel

I needed to take a Comma-Separated Values (CSV) file that contained a column of names, with all of the letters in upper case, e.g. "JOHN SMITH", and convert the names to a form where only the first letter of the first name and the first letter of the last name remained in upper case.

I opened the .csv file in Excel and used the proper() function to perform the conversion. Excel provides 3 functions, upper, lower, and proper to change the case of text.

Example: JOHN Smith is in cell A1 of an Excel worksheet

=UPPER(A1) will change the text to all uppercase, i.e. JOHN SMITH

=LOWER(A1) will change the text to all lowercase, i.e. john smith

=PROPER(A1) will change the case to suit a proper name, i.e. John Smith

There were about 1,500 entries in the worksheet, so I didn't want to type a formula in each cell. Instead, I created another column immediately to the right of the one containing the name by clicking on Insert then Columns in Excel. The first cell containing a name was B2. In C2, I put the formula =proper(b2). Then I clicked in the C2 cell to select it. I then held the left mouse button down and extended the highligted area down to the last row containing a name. Since the last such row was row 1482, I highlighted cells C2 through C1482. When I had all of the cells highlighted, I released the mouse button. I then hit Ctrl-D to copy the formula down through all of the higlighted cells (the formula is automatically incremented as it is copied). That resulted in the following formulas in column C.

CellFormula
B2=PROPER(B2)
B3=PROPER(B3)
......
B1481=PROPER(B1481)
B1482=PROPER(B1482)

I then resaved the file in csv format and closed it. I then reopened it in Excel. Since it was saved in CSV format, column C had the names with the correct capitalization now without any formula attached to them. I then deleted column B, which had the names in all capital letters. I then resaved the file.

Note: If you have a name in the form "JOHN S SMITH III", proper will convert the name to "John S Smith Iii", so you may need to scan through the list of names for such instances.

References:

  1. Properly Capitalize Text with "Proper"
    Lega Andrew - law for the rest of us
  2. text case conversion in excel
    Excel Lesson (97 and 2002)
    CastleJB.com

[/os/windows/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