Upper & Lowercase

  • Thread starter Thread starter Lynn
  • Start date Start date
L

Lynn

I have a last name column in which some of the names are
in all uppercase. Is there a way to change all the names
to where only the first character is uppercase and the
rest of the name is in lower case.

Thanks!
 
Lynn, or if you want to use a macro you could do it without having to add a
column, select the range you want to change before you run this macro

Sub Proper_Case()
'select the range you want to change
'and run this macro
Application.ScreenUpdating = False
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = Application.WorksheetFunction.Proper(Rng.Value)
End If
Next Rng
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Hi Lynn,
You say that some of the cells have all caps, it the other cells
are correct you may not want to apply a proper case macro
to those cells. If they are better than you would get with a macro
you might want to include an additional test
If rng.value = ucase(rng.value) then
ooo current replacement line ooo
end if

There is a macro on my page
http://www.mvps.org/dmcritchie/excel/proper.htm
that attempts to take care of names like McRitchie and de Bruin
where you have to code your own exceptions for some names.

You would invoke the macro Proper_case,
but you would need both macros: Proper_case() and proper_case_inner()

The purpose of splitting the macro was to be able to process
a different selection range from within another macro, but usage
would be transparent..
 
Back
Top