Hi Joe,
This is something commonly done in telephone directory
listings, and I am surprised that I've not seen this request
before in the newsgroups.
Macro solutions do not require any additional effort on
your part to remove introduced formulas, because they
will do the work in place on a selection.
Here is another macro solution involving three macros,
which will work much better for you. I have no idea what
you can do in Microsoft Word to implement this, but this
is for Excel using macros.
I changed my mind on the no comma part, since the
list will be sorted, will assume that no comma means a
last name only, and not an error.
The Surname_case macro calls another macro to
actually do the work but this is what it will do for
a selection when you invoke the macro.
Up through the comma would be boldface and upper case,
after the comma would be regular and proper case.
If there is no comma you will get boldface and upper case
for the entire cell. Although it would be easy to change
how this works.
I repeat just so this article is complete, If not familiar with
macros install the following in a regular macro, see
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Sub Surname_Case()
'-- This macro is invoked by you -- i.e. from Macro Dialog (Alt+F8)
Surname_Case_Inner 'The macro you invoke from a menu is Proper_Case
End Sub
Sub Surname_Case_Inner(Optional mySelection As String)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range, i As Long, rng As Range
'On Error Resume Next 'In case no cells in selection
If mySelection = "" Then
Set rng = Selection
Else
Set rng = Range(mySelection)
End If
For Each cell In Intersect(rng, _
rng.SpecialCells(xlConstants, xlTextValues))
cell.Formula = UCase(cell.Formula)
i = InStr(1, cell.Formula, ",")
If i = 0 Then
cell.Font.FontStyle = "Bold"
Else
cell.Formula = Left(cell.Formula, i) _
& Application.Proper(Mid(cell.Formula, i + 1))
With cell.Font
.FontStyle = "Bold"
End With
With cell.Characters(Start:=i + 1).Font
.FontStyle = "Regular"
End With
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Now after running the above macro, you might want changes
to column 2 (col B) to be automatic. You would use an Event
macro for this, which is installed differently. You can read
about them later in
http://www.mvps.org/dmcritchie/excel/event.htm
Right click on the worksheet tab, view code, plop this in
and it will ONLY apply to this worksheet. My assumption
is that you have a phone number in column A, the name
in Column B, additional information in remaining columns.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.row = 1 Then Exit Sub 'don't override headings in row 1
If Target.Column <> 2 Then Exit Sub 'only allow changes to Col B
Application.EnableEvents = False
Application.Run "pesonal.xls!Surname_Case_inner", Target.Address
Application.EnableEvents = True
End Sub
For more information on how these macros work and why you
would NOT want to place additional worksheet functions
on the worksheet as in the Excel only solutions provided
in some of the other responses, see
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm
Once you've installed and used these macros, you should
be aware that the newsgroups
for working with macro solutions is excel.programming
where it will be assumed you know how to install and use
a macro.
Some people manage to do all of the above the first time
in fifteen minutes, but I think an hour of your time is more reasonable
for the first time installing and using macros and well worth the time spent.
