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)JOHN SMITH
=LOWER(A1)john smith
=PROPER(A1)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.
| Cell | Formula |
|---|---|
| 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:
-
Properly Capitalize Text with "Proper"
Lega Andrew - law for the rest of us -
text case conversion in excel
Excel Lesson (97 and 2002)
CastleJB.com
