Excel

Convert Text to Title Case

Ease of Use

Easy

Version tested with

2000, 2002 

Submitted by:

Justinlabenne

Description:

Convert a cells text to Proper Case (First Letter of each word is Upper Case) 

Discussion:

Useful if a user needs to enter Names or book titles or another form of a text string that is best suited in proper case format. This code works to ensure that the text entered into column A only will be in Proper case format. The first letter of each word is capitalized. 

Code:

instructions for use 

			
Option Explicit 
 
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    Dim lRow As Long 
    lRow = Cells(Rows.Count, 1).End(xlUp).Row 
     
     
     '   If it's not Column A, then we don't run
    If Intersect(Range("A1:A" & lRow), Range(Target.Address)) _ 
    Is Nothing Then Exit Sub 
     
     
     '   Stop other events
    With Application 
        .EnableEvents = True 
         
         
         '       Errors if we clear ranges of cells in target column
        On Error Resume Next 
         '       Convert the target text to Proper case
        Target.Value = Application.Proper$(Target.Value) 
         
         
         '        Re-Enable events
        .EnableEvents = True 
    End With 
End Sub

How to use:

  1. Open an Excel Workbook
  2. Copy the code
  3. Right Click on a Sheet tab > View Code
  4. Paste code into the right pane
  5. Press Alt+Q to return to Excel
  6. Save workbook before any other changes
 

Test the code:

  1. Type some text in column A and it will automatically be changed to Proper Case: (First letter of each word Upper case)
 

Sample File:

ConvertToProper.zip 8.22KB 

Approved by mdmackillop


This entry has been viewed 163 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express