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

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
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200902/1

On Fri, 13 Feb 2009 22:31:01 -0800, Bob76
wrote:

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

Hi

Its 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 its useful for u.

TGV


"Bob76" wrote:

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

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

Bob

"Lars-Ã…ke Aspelin" wrote:

On Fri, 13 Feb 2009 22:31:01 -0800, Bob76
wrote:

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

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)

--
Rick (MVP - Excel)



"Chris Bode via OfficeKB.com" <u49096@uwe wrote in message news:91aee11615b0d@uwe...
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
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200902/1