Add Change Case Buttons to the Microsoft Excel 365 Ribbon

If you work with both Microsoft Word and Microsoft Excel, you may have noticed that Microsoft Word has a “change case” button in the Font section of the ribbon which allows you to quickly capitalize or lowercase sections of your document. While Excel does not come with such a button by default, it is possible to add buttons to set the case of the highlighted cells.

Step 1: Add New Macros to Your Personal Macro Workbook

The personal macro workbook allows you to store macros that you would like to use in multiple workbooks. Once we’ve added macros to change case to the personal macro workbook, we can add buttons for them on the ribbon. Ensure the developer tab is enabled on the ribbon. Then, to make sure you have a personal macro workbook, go to Developer > Record Macro and record a macro. Stop recording, and open the Visual Basic editor from the same tab. In place of the recently recorded macro, add the following code:

Sub UpperCase()
'
' UpperCase Macro
'

'
    Dim cell As Range
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = UCase(cell.Value) ' Capitalize the text
        End If
    Next cell
End Sub

Sub LowerCase()
'
' LowerCase Macro
'

'
    Dim cell As Range
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = LCase(cell.Value) ' Lower case the text
        End If
    Next cell
End Sub

Sub ProperCase()
'
' ProperCase Macro
'

'
    Dim cell As Range
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            cell.Value = StrConv(cell.Value, vbProperCase) ' Proper case the text
        End If
    Next cell
End Sub

Step 2: Add Change Case Buttons to the Ribbon

Right click the ribbon, and click “Customize the Ribbon…”. On the left, select Macros and move the macros into the desired area of the ribbon. Use the “Rename…” button to name the buttons and select an icon if desired.

That’s it! Because these macros are associated with your personal macro workbook, you can use the new buttons any time you are using Excel.

Validate and Clean Canadian Postal Codes in Microsoft Excel

If you want to cleanup and validate a list of Canadian postal codes in Microsoft Excel, you’ve come to the right place! With the formulas below, you can ensure postal codes are properly formatted and conform to the standard postal code format.

Step 1: Clean the Data

The formula below will remove any excess white space, and ensure that the postal code is formatted with uppercase letters and a single space in the middle. If this step is not done, the validation formula in step 2 may falsely report a postal code as invalid when it is simply improperly formatted.

=LEFT(TRIM(SUBSTITUTE(UPPER(A1), " ", "")),3)&" "&RIGHT(TRIM(SUBSTITUTE(UPPER(A1), " ", "")),3)

Step 2: Validate the Postal Code

The formula below will return TRUE/FALSE depending on whether the postal code is found to be valid. The formula ensures that the postal code has letters and numbers in the correct places, and is exactly 7 characters in length (including the expected space in the middle). It also ensures that the postal code does not contain letters that are not found in valid postal codes. This is done using the EXACT function to find whether the letter appears in a list of accepted letters. Since we pass an array to EXACT, we will receive an array back. The MATCH function is used to determine if there was a match with any of the letters in our list. If the letter wasn’t found in the list, EXACT will return an array of FALSE and MATCH will return an error. Since the error value is not a number, our formula will return false in that case.

# Easy to read format
=AND(
    LEN(B1)=7,
    ISNUMBER(VALUE(MID(B1,2,1))),
    ISNUMBER(VALUE(MID(B1,5,1))),
    ISNUMBER(VALUE(MID(B1,7,1))),
    ISNUMBER(MATCH(TRUE, EXACT(MID(B1, 1, 1), {"A","B","C","E","G","H","J","K","L","M","N","P","R","S","T","V","X","Y"}), 0)),
    ISNUMBER(MATCH(TRUE, EXACT(MID(B1, 3, 1), {"A","B","C","E","G","H","J","K","L","M","N","P","R","S","T","V","W","X","Y","Z"}), 0)),
    ISNUMBER(MATCH(TRUE, EXACT(MID(B1, 6, 1), {"A","B","C","E","G","H","J","K","L","M","N","P","R","S","T","V","W","X","Y","Z"}), 0)),
    MID(B1,4,1)=" "
)

# Same as above but easier to copy into Excel
=AND(LEN(B1)=7,ISNUMBER(VALUE(MID(B1,2,1))),ISNUMBER(VALUE(MID(B1,5,1))),ISNUMBER(VALUE(MID(B1,7,1))),ISNUMBER(MATCH(TRUE, EXACT(MID(B1, 1, 1), {"A","B","C","E","G","H","J","K","L","M","N","P","R","S","T","V","X","Y"}), 0)),ISNUMBER(MATCH(TRUE, EXACT(MID(B1, 3, 1), {"A","B","C","E","G","H","J","K","L","M","N","P","R","S","T","V","W","X","Y","Z"}), 0)),ISNUMBER(MATCH(TRUE, EXACT(MID(B1, 6, 1), {"A","B","C","E","G","H","J","K","L","M","N","P","R","S","T","V","W","X","Y","Z"}), 0)),MID(B1,4,1)=" ")