Changing Cases in Microsoft Excel

There are three functions in Microsoft Excel directly related to changing the case of text. Those functions are:

=Upper()
=Lower()
=Proper()

The premise is simple, if you have the word “Excel” in cell A1. And you type this function into cell A2:

=Upper(a1)

Your result would be, “EXCEL”. If you have columnar data, these functions are great for changing all uppercase words into Proper case (for example, a list of first names that happened to be in all upper or mixed upper and lowercase). There are a couple of issues when working with large batches of data that need case-changing:

You have to devote an entirely separate column to holding the formulas, then copy and paste the values back over the other column in order for the change to be complete.
You’ve got to basically re-arrange your worksheet if you’re changing values in multiple columns.

Our first function helps out quite a bit if you need to change case data and don’t feel like inserting new columns and dragging formulas around to do it.

Copy and paste this function into a new module to give it a try:

Sub ChangeCaseUsingInputBox()
Dim CS As String
Dim cell As Variant

CS = InputBox("To convert the selected text to Upper, Lower or Proper case please type the appropriate word (upper, lower, proper)")

CS = LCase(CS)

Select Case CS

Case Is = "upper"
  For Each cell In Selection
  cell.Value = UCase(cell.Value)
  Next cell

Case Is = "lower"
  For Each cell In Selection
  cell.Value = LCase(cell.Value)
  Next cell

Case Is = "proper"
  For Each cell In Selection
  cell.Value = Application.WorksheetFunction.Proper(cell.Value)
  Next cell

End Select

End Sub

This function uses an Inputbox to ask you for what case you want the text to be in, instead of the likely more conventional route of userforms to have you select a choice – though the inputbox does work just as well.

By using the Select Case statement instead of a string of IF statements the code tends to look a little cleaner though either method is truly fine as there are only three choices made available. Certainly you can change up this formula to do many tasks with selected words. If you’re bold enough, you can create a system that changes every word in your Excel Worksheet into piglatin. Though that might just be a monumental waste of time.

When you’re ready to use the function, simply highlight all of the text that you want to change on your worksheet, then click Tools, Macro, Macros and select the “ChangeCaseUsingInputBox” macro and off you go!

David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert. David's unique ability to easily explain virtually any technical concept has allowed him to work with companies such as AMD, Motorola, the American Heart Association, and countless small businesses. Learn more about database design right now at http://www.bluemoosetech.com.

Article Publishing and Re-Print Guidelines

The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
When re-published online, all links must be live hyperlinks.
These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!

You can easily link to this article!

If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:

On your page, the code will appear like this:

Changing Cases in Microsoft Excel