Turning capitals into lower case

[lisanappi]

I work in the fundraising department and therefore have very basic Exce
skills. I just received a spreadsheet containing all employee name
and addresses which I need to merge into a letter. The only problem i
that all of the data in the sheet was keyed in using CAPS LOCKS and
need to convert the data to lower case letters EXCEPT for the firs
letter of the individual's names.

I have been told there is a formula or code that can do this for me
however I can't seem to find that information anywhere. Could anyon
help?

Thanks in advance.

Lisa Nappi
New England Baptist Hospital
Boston, MA
617.754.665

 

[Lady Layla]

The function you need is =Proper(cell address)

: I work in the fundraising department and therefore have very basic Excel
: skills. I just received a spreadsheet containing all employee names
: and addresses which I need to merge into a letter. The only problem is
: that all of the data in the sheet was keyed in using CAPS LOCKS and I
: need to convert the data to lower case letters EXCEPT for the first
: letter of the individual's names.
:
: I have been told there is a formula or code that can do this for me,
: however I can't seem to find that information anywhere. Could anyone
: help?
:
: Thanks in advance.
:
: Lisa Nappi
: New England Baptist Hospital
: Boston, MA
: 617.754.6651
:
:
: ---
:
:

 

[Harald Staff]

Hi Lisa

Select the cells and run this macro:

Sub Proper_Case()
Dim Cel As Range
On Error Resume Next
For Each Cel In Intersect(Selection, ActiveSheet.UsedRange)
Cel.Formula = StrConv(Cel.Formula, vbProperCase)
Next
End Sub

The code will not know the difference between a name and an address, but
that's as far as computing goes these days.

HTH. Best wishes Harald

 

[Don Guillett]

try putting this in your personal.xls and using when needed.
Sub ChangeCase()
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub

 

[David McRitchie]

Hi Lisa,
Having worked with last names for a telephone directory
(not with Excel), I can suggest that you want to use a macro
to cover exceptions that you are likely to encounter. Since
the macro changes names in place you can go over them and
correct them to how they actually do their names. You will get
to know the areas that might be a problem such as
de der Mac Mc van von most of these will be taken care
of in the macro but you will have to doublecheck because
some people with the same name (letter by letter) may
capitalize and pronounce there names entirely differently.
i.e. Mack, Mackie, Mackey, MacKey, .

See http://www.mvps.org/dmcritchie/excel/proper.htm#proper
there are two macro to include, one is an interface for
your use to a macro that can be called form other macros.
It will handle such common names as "McRitchie" and "de Bruin".
well maybe common in this newsgroup.