Surnames, Special formatting for LASTNAME, firstname

Location: http://www.mvps.org/dmcritchie/excel/surname.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

description:  Uppercase bolded surname followed by plain style given name, for example:  DUMFRIES, Jonathan
This page contains some VBA macros.  If you need assistance to install or to use a macro please refer to my  «Getting Started with Macros« or delve into it deeper on my Install  page.

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

 ABCDEFG
 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.


Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions.  Posting suggestions and netiquette.  More information on newsgroups and searching newsgroups.    Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).
This page was introduced on March 31, 2005. 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

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


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