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,
proper to change the case of text.
JOHN Smith is in cell A1 of an Excel worksheet
will change the text to all uppercase, i.e.
will change the text to all lowercase, i.e.
will change the case to suit a proper name, i.e.
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.
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",
will convert the name to "John S Smith Iii", so you may need to scan through
the list of names for such instances.
Properly Capitalize Text with "Proper"
Lega Andrew - law for the rest of us
text case conversion in excel
Excel Lesson (97 and 2002)