uppercase to lowercase

[Bob76]

Hi,
How do I convert names from all uppercase to lowercase, but leave the first
letters of the names in uppercase? for example: SMITH, JOHN to Smith,
John. I have about 150 names i have to convert in Excel 2007 spreadsheet and
it's really tedious to do it one at a time. Hope someone can help....
Thanks a lot, Bob

 

[Chris Bode via OfficeKB.com]

Here I suggest you a solution with macro
Let us suppose that the names are in column A of sheet1 then,

1.Right click on toolbar check the control box
2.Add a command button to your sheet
3.Double click the button to open code window and paste following codes

#
Private Sub CommandButton1_Click()
Dim row As Integer, col As Integer
row = 1
col = 1

While Sheet1.Cells(row, col).Value <> ""
Sheet1.Cells(row, col).Value = UCase(Left(Sheet1.Cells(row, col).
Value, 1)) & LCase(Mid(Sheet1.Cells(row, col).Value, 2, Len(Sheet1.Cells(row,
col).Value)))

row = row + 1
Wend
End Sub
#

Now on clicking the button, you get the job done!


Hope this works

Have a nice time

Chris

 

[Lars-Åke Aspelin]

Hi,
How do I convert names from all uppercase to lowercase, but leave the first
letters of the names in uppercase? for example: SMITH, JOHN to Smith,
John. I have about 150 names i have to convert in Excel 2007 spreadsheet and
it's really tedious to do it one at a time. Hope someone can help....
Thanks a lot, Bob

Take a look at the PROPER() function
http://www.techonthenet.com/excel/formulas/proper.php

If the names are in a column you can use a helper column with the
PROPER() function, copy the helper column back to the names column,
and finally remove the helper column.
Use "Paste Special" in the copying process.

Hope this helps / Lars-Åke

 

[TGV]

Hi

It’s very simple.

Try it

=proper(A1)

Change the cell reference, here I have mentioned it as a1 change it as per
your requirement.

Copy and paste it for the remaining cells.

Hope it’s useful for u.

TGV

 

[Bob76]

I did it....Thanks a lot for your tip.... converted both last names and first
names all in 2 minutes.

Bob

 

[Rick Rothstein]

Sheet1.Cells(row, col).Value = UCase(Left(Sheet1.Cells(row, col).

Value, 1)) & LCase(Mid(Sheet1.Cells(row, col).Value, 2, Len(Sheet1.Cells(row,
col).Value)))

You could replace the above statement from your code with this...

Sheet1.Cells(Row, Col).Value = StrConv(Sheet1.Cells(Row, Col).Value, vbProperCase)