Proper, and other Text changes

If you want the contents of formulas to also be converted use Bill Manville's code or a variation. (see related below)

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

keywords:  capitalization, lettercase, lower case, lowercase, lower-case, proper case, title case, upper case, upper-case, uppercase

PROPER

You can change your all uppercase to PROPER case with a worksheet function.  This can help with a list of names and addresses that was in all uppercase (capitals) or lowercase.  This is only to help fix the problems it will not properly fix names like "McRitchie" or "van der Beck".    (Well it fixes my name now -- just as an example, of course).

HELP --> Index --> PROPER Worksheet Function

=PROPER(a1)

Using a worksheet function you would have to copy then paste special and delete the original afterwards.

A macro works a lot easier.  Simply select a range of cells and invoke the macro.  Include additional code in macro to speed up results.

Sub Proper_Case()
     Application.ScreenUpdating = False
     Application.Calculation = xlCalculationManual
     Dim Cell as Range
     On Error Resume Next   'In case no cells in selection
     For Each Cell In Intersect(Selection, _
            Selection.SpecialCells(xlConstants, xlTextValues))
        Cell.Value = Application.Proper(Cell)
        '--- this is where you would code generalized changes for lastname
        '--- applied to names beginning in position 1 of cell
        If Left(Cell.Value, 2) = "Mc" Then Cell.Value = _
           "Mc" & UCase(Mid(Cell.Value, 3, 1)) & Mid(Cell.Value, 4, 99)
        If Left(Cell.Value, 3) = "Mac" Then Cell.Value = _
           "Mac" & UCase(Mid(Cell.Value, 4, 1)) & Mid(Cell.Value, 5, 99)
     Next
     '-- some specific text changes to lowercase, not in 1st position
     Selection.Replace What:=" a ", Replacement:=" a ", _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
     Selection.Replace What:=" and ", Replacement:=" and ", _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
     Selection.Replace What:=" at ", Replacement:=" at ", _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
     Selection.Replace What:=" for ", Replacement:=" for ", _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
     Selection.Replace What:=" from ", Replacement:=" from ", _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
     Selection.Replace What:=" in ", Replacement:=" in ", _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
     Selection.Replace What:=" of ", Replacement:=" of ", _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
     Selection.Replace What:=" on ", Replacement:=" on ", _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
     Selection.Replace What:=" the ", Replacement:=" the ", _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
     '--- This is where you would code specific name changes
     '--- regardless of position of character string in the cell
     Selection.Replace What:="mcritchie", Replacement:="McRitchie", _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
     Selection.Replace What:="delouis", Replacement:="DeLouis", _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
     Application.Calculation = xlCalculationAutomatic
     Application.ScreenUpdating = True
  End Sub

Some notations on the above code

• Turning off Screen updating keeps the screen from refreshing with each cell change and improves performance.
• Turning off Calculation even though you don't see much in the way of calculations greatly improves performance.  In XL95 use xlManual and xlAutomatic instead.  (there is no numeric in these constants it is XL not X1).
• DIM statement for each variable is recommended and is required with use of Option Explicit which in included at the top of modules in Excel 2000.
• Testing for constants eliminates need to test for formula .. If cell.HasFormula = False Then ...
• Reducing the cell selection to Text constants eliminates the need to check for a used cell range.  Failure to make a reduction of one or the other would take forever if say you selected an entire column as there are a lot of rows in all versions of Excel. 
• There are 16,384 rows in XL95, and 65,536 in XL97 and up.  In other words we do what we can to not check all 65,536 rows by 256 columns (16,777,984 cells), which might be what was selected.
• Without the use of he use of INTERSECT a single cell selection would default to the entire worksheet limited to special cells designation.  But the INTERSECT keeps the selection at one cell (or less) when starting with a single cell selected as in just one active cell.  This is a lot easier than using an IF to check the selection.count

     Intersect(Selection, Selection.SpecialCells(xlConstants, xlTextValues))
  
     Intersect(Selection, ActiveSheet.UsedRange)
  
     Intersect(Selection, ActiveSheet.UsedRange, ActiveCell.EntireColumn)
  
     If Intersect(ActiveCell, Range("YellowRange")) Is Nothing Then
       MsgBox "Active cell is not in range of YellowRange =" & yellowrange.addresslocal(0,0)
  

        XlSpecialCellsValue constants:  xlErrors, xlLogical, xlNumbers, or xlTextValues.

  
  SpecialCells(xlCellTypeFormulas, 23).Select 'All formulae
  SpecialCells(xlCellTypeFormulas, 16).Select 'All formulae with errors
  SpecialCells(xlCellTypeFormulas, 2).Select 'All formulae with text
  SpecialCells(xlCellTypeFormulas, 4).Select 'All formulae with logic
  SpecialCells(xlCellTypeFormulas, 6).Select 'All formulae with text or logic
   

• This could result in no cells selected, so an error test in front
     On Error Resume Next 'In case no cells in selection
• You could be more specific using NOTHING in a test instead of coding for a general Error, but you still have to provide an Error check and will have to use even more coding. 

    Dim rng As Range
    On Error Resume Next
    Set rng = Intersect(Selection, _
       Selection.SpecialCells(xlConstants, xlTextValues))
    On Error GoTo 0
    If Not rng Is Nothing Then
        For Each Cell In Intersect(Selection, _
            Selection.SpecialCells(xlConstants, xlTextValues))
                           ...
        next cell
    End If
  End Sub
  

• Simple macros to CLEAR selection of constants.  For an example of clearing constants see InsertRowsAndFillFormulas Macro macro.

  Sub ClearNumberConstants()
    On Error Resume Next 
    Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlNumbers)).Clear
  End Sub
  
  Sub ClearConstants()
    On Error Resume Next 
    Intersect(Selection, _
      Selection.SpecialCells(xlConstants)).Clear
  End Sub
  

• There are words that are normally not capitalized in titles and when using Proper Case.  These are embedded words such as: a, and, at, for, from, in, of, on, and the.  Also a fixup for my own name whereever it occurs in the selection.
• So what seems at first seems like very simple code should prevail actually requires more code than a simple example of something that works.
• If your intent is to change people's names that begin with "Mc", "Mac", etc. then I would suggest using a modified macro that specifically is to work with the names you will be working with, because the more code you add you will have to make manual changes later. "Mc" is a safe change in position 1, "Mac" would be less so, "De", "Di", "Le", "van", "von" etc will depend on who they are and how they really spell their own name.  Mackey is not the same as MacKey, so it would come down to knowing your data, because you would have to make manual changes afterwards or provide for all exceptions in your code.
• For sorting purposes, in a different macro, you might want to eliminate specific article prefixes at the beginning of book titles of "A ", "An ", and "The ".

     If Left(Cell.Value, 2) = "A " Then Cell.Value = Mid(cell.value,3,999)
     If Left(Cell.Value, 3) = "An " Then Cell.Value = Mid(cell.value,4,999)
     If Left(Cell.Value, 4) = "The " Then Cell.value = Mid(cell.value,5,999)
  

Example of Current Region involved when a single cell is selected

As previously described here is an example where a single cell is selected but the use of "Selection.SpecialCells(xlCellTypeConstants)" would cause cell involvement from the current region instead of a maximum of 1 cell.

  Sub DemoSingleCellSelect()
    Dim cell As Range
    Range("a1:k10") = "aa"   '-- try later with  = "=1+1"
    Range("a3:f4").Clear
    Range("a1").Select
    on error resume next    '-- in case no cells selected
    For Each cell In Selection.SpecialCells(xlCellTypeConstants)
       cell.Interior.colorindex = 38
   Next cell
  End Sub

Things to watch out for when using macros, your own or someone else's

• You want the macro to be as generic as possible:  Look to see if you can use a selection of 1 cell, of a range, of multiple ranges, of the entire sheet.  The inability to do each of these does not mean there is a problem with the macro, but the idea is to make things as generic as possible, and to know how it works.  You normally do not want to make something production that addresses only specific cells.
• If a macro addresses all cells in a selection you might want to reduce that considerably to prevent it from performing useless processing.  Select the current region (Ctrl+Shift+*) or all cells in the used range (Ctrl+End to get last cell, then Ctrl+Shift+Home to extend back to first cell)
  See Excel Shortcuts, some keys will not work as indicated if Transition Navigation is in effect.
• Another way to limit usage is using Special Cells and limiting usage to constants, formulas, blanks (ISBLANK).

The following could be substituted in these macros: Cell.Value = StrConv(Cell,vbProperCase)

Here is a solution from Tom Ogilvy 1999-02-07) that changes cells that are constants on the ENTIRE spreadsheet.  The macro provided at the top will do the same if you Select ALL cells first before invoking the macro, but you might want to compare what is the same and what differs.  One big difference will be the speed because it has not turned off calculation and screen updating.

   Sub MakeProper()
     Dim Cell as Range, rng1 as Range
     Set rng1 = ActiveSheet.UsedRange.SpecialCells(xlConstants, xlTextValues)
     For Each cell In rng1
        cell.Value = Application.Proper(cell.Value)
     Next cell
   End Sub

For XL97 and above use xlCellTypeConstants instead of xlConstants

Lowercase

The worksheet function LOWER works the same as the PROPER worksheet function.

Example of a macro: (turn off screen updating and calculation in macro for quicker results)

Sub Lower_Case()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual          'in XL97
   Dim Cell as Range
   On Error Resume Next   'In case no cells in selection
   For Each Cell In Intersect(Selection, _
          Selection.SpecialCells(xlConstants, xlTextValues))
      Cell.Value = LCase(Cell.Value)
   Next
   Application.Calculation = xlCalculationAutomatic     'in XL97
   Application.ScreenUpdating = True
End Sub

Uppercase

The worksheet function UPPER works the same as the PROPER worksheet function.  I would have to say that except for zip state codes I have not much use for converting everything to uppercase.

Example of a macro: (turn off screen updating and calculation in macro for quicker results)

Sub Upper_Case()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim Cell as Range
   On Error Resume Next   'In case no cells in selection
   For Each Cell In Intersect(Selection, _
          Selection.SpecialCells(xlConstants, xlTextValues))
      Cell.Value = UCase(Cell.Value)
   Next
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

If appearance is all that matters there are some uppercase only fonts like Bank Gothic Mb BT, CopprplGoth [Hv] BT (Cooperpot), Desdemona, Bassoon.  You can find what you actually have by bringing the Unicode Character Map (shortcut) typing in your name in mixed case, and using the ArrowDN key to cycle through the fonts.  Excel and other windows software does not provide access to codepages which could have been built to have upppercase only.  Subset under font is as close as you will get to codepages.

The following would change entries immediately as they are created using an Event macro.  An Event Macro is apparently not effective until the workbook containing the macro is saved.  Note event macros are stored with the worksheet unlike normal macros which are stored in modules.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'to install -- rightclick on the sheettab of the corresponding
' sheet and choose 'view code'.  Paste the following procedure
' in the module.  -- this is for EVENT macros ONLY.
  If Target.Column <> 1 Then Exit Sub
  If Target.Row = 1 Then Exit Sub
  Application.EnableEvents = False
  Target.Formula = UCase(Target.Formula)
  Application.EnableEvents = True
End Sub

TrimSUB - trim cells in selection

The TRIM Worksheet function removes spaces from beginning and end of text cells as well as extra internal spaces.  Removes all spaces from text except for single spaces between words.  The VBA equivalent only trims leading and trailing spaces.  (turn off screen updating and calculation in macro for quicker results)

Sub TrimSUB()
   Dim icell as Range
   For Each icell In Intersect(Selection, _
         Selection.SpecialCells(xlConstants, xlTextValues))
      icell.Value = Trim(icell.Value)
   Next icell
End Sub

Insert Blank Rows

For this example 2001-10-02, the poster requested that 2 blank rows be inserted for when the next cell in selected column is greater than the cell in the previous row of the column.  Note use of INTERSECT.

Sub InsertBlankRows()
    Dim i As Long   'integer not sufficient
    Dim nRange As Range
    Set nRange = Intersect(Selection, ActiveSheet.UsedRange, _
       ActiveCell.EntireColumn)
    If nRange.Cells.Count < 2 Then Exit Sub
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For i = nRange.Cells.Count To 2 Step -1
       If nRange.Item(i) > nRange.Item(i - 1) And _
            Not IsEmpty(nRange.Item(i - 1)) Then
            nRange.Item(i).Resize(2, 1).EntireRow.Insert
       End If
    Next
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

The example was included here because of the use of INTERSECT and the manner of the loop, more comprehensive information on Inserting Rows.

Select cells matching activecell in the Selection area

Sub S_active()
  Dim cell As Range
  Dim i As Long
  Dim sStr As String
  For Each cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
    i = i + 1
    If cell.Value = ActiveCell.Value Then _
       sStr = sStr & "," & cell.Address(0, 0) _
       & ":" & cell.Address(0, 0)
  Next cell
  Range(Mid(sStr, 2, Len(sStr) - 1)).Select
End Sub

Checking for Numeric Cells in a Worksheet Selection

The following may look a bit out of place on this page, but is included here because of use of celltypes ie. xlCellTypeFormulas, xlCellTypeConstants. 

You can make similar macros named chkText and chkAllText to check for text instead by using xlTextValues instead of the 1 used in place of xlNumbers.

Sub chkAllNumeric()
    Cells.Select
    chkNumeric
End Sub
Sub chkNumeric()
    'David McRitchie in misc  2001-04-14  will be in proper.htm
    '   http://www.mvps.org/dmcritchie/excel/proper.htm
    'Something similar can be done with C.F.  =ISNUMBER(A1)
    Dim fNumeric As Range, cNumeric As Range
    On Error Resume Next
    Set fNumeric = Selection.SpecialCells(xlCellTypeFormulas, 1)
    Set cNumeric = Selection.SpecialCells(xlCellTypeConstants, 1)
    If fNumeric Is Nothing Then
       If cNumeric Is Nothing Then
          MsgBox "No numeric cells in selection"
       Else
          cNumeric.Select
       End If
    Else
       If cNumeric Is Nothing Then
          fNumeric.Select
       Else
          Union(fNumeric, cNumeric).Select
       End If
    End If
End Sub

An unreliable visual indicator would be to look at the cell: if left justified text, if right justified numberic. This can be overridden by cell justification, so select the cell and make sure it is not specifically overridden with left/center/right justified.

To check a lot of cells would be to select all cells (Ctrl+A) then Edit menu, GoTo, [Special],
    Constants & Number
    Formulas & Number
Unfortunately you can't do both at same time. Also note this is where you would choose "Blanks" to see if a cell is really Empty (no content, no formulas).

Use a Worksheet Function on an individual cell
    =ISTEXT(A1)
    =ISNUMBER(A1)

Use a Conditional Formatting with =ISNUMBER(A1) with one color and =ISTEXT(A1) with another color as described by Rob Bovey. Conditional Formatting will do no damage to your Excel File. To remove C.F. you will have to find C.F. usage which you can do by selecting a cell identified in the range and then Format --> C.F. as was used in creation.

AutoCorrect

There are some things that cause people problems with Auto Correct (Tools menu, Autocorrect)

• Occassionaly someone has a problem with getting a symbol instead of what they typed -- look for something in AutoCorrect.
• Text entered as "Mr. and Mrs. Jackson" gets converted to "Mr. And Mrs. Jackson" this is because the period after Mr is recognized as an end of sentence.  The solution is to use the Exceptions button on the AutoCorrect menu and add "Mr." into it.  Everything there is an abbreviation ending with a period.

Related Items

How to install/use a macro can be found on my formula

HELP --> StrConv     [vbUpperCase, vbLowerCase, vbProperCase]
= StrConv(Cell,vbLowerCase) page.

Related Information on My Excel Pages:

• Rearranging Data in Columns -- Rearrange columns by splitting, joining, or reversing columns of data.  Join() can be used as a reversal of a correctly executed text to columns.  Lastname() can be used as a put lastname first is not already done.  SepTerm() can be used as a more limited version of text to columns that only separates from the first word. SepLastTerm() separates last word from the beginning of text.  ReversI() is used to reverse the order of items in a row, column, or range.  MarkCells() is used to create test data within the invoked range.  Also includes use of fill handle and creating a sheet with short cut keys for reference.
• Slow Response in Excel and the means of speeding up some things.

Related Information in Google Usenet Archives

• Re: Change Case a reply posting by Bill Manville.  Bill's code will also handle cells with formulas by enclosing the formula rather than simply changing data.  Actually in most cases changing the input will have already done this.
• Re: Leading capital a reply posting by Harold Staff.  Harold's code to capitalize only the first letter of the first word in a cell.
• Re: Forcing Capitalization a reply posting by Chip Pearson applicable to XL97 using a Private Worksheet_Change event procedure.

Microsoft Knowledge Data Base (MS KB)

Q107564 XL: Not All Worksheet Functions Supported as Application (in VBA)

Run-Time Error '438':

Object doesn't support this property or method

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to:  David McRitchie  mailto:DMcRitchie@msn.com.