Surnames, Special formatting for LASTNAME, firstname

---

description:  Uppercase bolded surname followed by plain style given name, for example:  DUMFRIES, Jonathan

---

Coding for this page, is available in another directory with names corresponding to some of the HTML files in the current directory.

keywords:  surname, surnames, lettercase

Special capitalization and formatting for surnames

	A	B	C	D	E	F	G
1	Phone	Member	Address	City	ST	Zip+4	email
2	412-555-1212	BROWN, Mark	4210 Washington Rd	Pittsburgh	PA	15228-3000	mbrown@example.com
3	203-484-1234	TAYLOR	714 Old Post Rd	North Branford	CT	06471	ktaylor@example.com
4	505-924-1212	WATSON, James	240 E Main St	Albuquerque	NM	87103-8123	jim_watson@example.com

This is a specialized example but it is based on coding that in shown on my Proper, and other Text changes -- Use of Special Cells.  The initial newsgroup posting for this was Re: Changing letter case to user specified., and you may very well expect that Google will screw up the coding text with their webpage presentation (nontext presentation) and their Google AdSense marketing, so I have chosen a Google UK site to bypass the disastrous Google Groups with a Remediation, and have included the code below.

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.

---

If not familiar with macros install the following in a regular macro, see  Getting Started with Macros

---

Option Explicit
Sub Surname_Case()
      '-- This macro is invoked by you -- i.e. from Macro Dialog (Alt+F8)
      Surname_Case_Inner    
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

After running the above macro, you might want changes to column 2 (col B) to be automatic.  To accomplish that you would use an Change Event macro, which is installed differently than regular macros. 

Right click on the worksheet tab, view code, plop this in and it will only apply to the worksheet it is installed to.  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 "personal.xls!Surname_Case_inner", Target.Address
  Application.EnableEvents = True
End Sub

Additional Notes

«Advantages of a macro:« while you can use  the worksheet functions  PROPER, UPPER, LOWER to change cells one at at time into another cell it is unlikely that you want to maintain two cells with the same value.   First you had to insert a helper column then carefully use the fill-handle to include all cells but not go too far (usually a double-click).  To clean up after worksheet functions you have to remove the dependency on the original by converting to constants (Copy using Ctrl+C then Edit, Paste Special, values) then you can delete the column of original entries.  With a macro the change is done in place, you can select entire columns or other selection, invoke the macro and you are finished.  A properly written macro will not process empty cells nor cells beyond the UsedRange and that is where the use of SpecialCells comes in.

Warnings:  If you have values that are defined as text values by preceding with a quote.  The macros regardless of whether they use .formula or .value will select text values based on SpecialCells, but will remove the single quote and text values that look like numbers would then become numbers if the format is General.

SpecialCells -- Usage warning, it has recently come to our attention (2004-01-11) that Special Cells has the following limitation (MS KB 832293).  If you select more than 8,192 non-contiguous cells with your macro, as may easily do with SpecialCells, Excel only supports a maximum of 8,192 non-contiguous cells through VBA macros.

Related   (#related)

• Proper, and other Text changes -- Use of Special Cells. 
• Event Macros, Worksheet Events and Workbook Events
• Slow Response, Memory Problems, and Speeding up Excel

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2005,  F. David McRitchie,  All Rights Reserved