How to Change Case in Excel

Capabilities and limitations to change case in excel

We use Microsoft excel a lot to process data with numerical values. However, at the same time it is required to process text based values. Sometimes when we get a data set with texts, it is not in the correct-formatting what we expected.

Case changing is one of the primary text formatting option we can do on newly imported text based data set. Unfortunately, change case in excel is not straight forward as in Microsoft word. You have to use excel worksheet functions for that purpose.

Worksheet functions are the ones, which we can insert from insert function command or by directly typing into the required cell. Currently there are more than 450 built in worksheet functions.

In addition to that, there are lots of available Add-ins to extend these limitations. Out of those in built worksheet functions, there are three functions to change case in excel to lowercase, UPPER CASE and Sentence case. After simple explaining these already available functions I am going to explain how to built a function to change texts to “Sentence Case”. And you can freely download a Excel file with the custom function at the end which can be used to change case in excel to sentence case.

01) Excel “UPPER ()” function

This function can be used to covert a string within a cell to UPPER CASE. That means all the letters in the sentence converted into capital letters. In the example shown in below figures, you can easily understand how it is used to change case in excel to UPPER case.

02) Excel “LOWER ()” function

This function can be used to covert a string within a cell to lower case where all the letters in the sentence converted into simple letters. As previous, you can use this function within a cell to change a string to lower case.

03) Excel “PROPER ()” function

Similarly,this function can be used to covert a string within a cell to proper case where first letter of each word turns into a capitol letter

04) Is there a Excel “SENTENCE ()” function?

Have you ever searched for a button or shortcut to change some unorganized sentence with mixed case characters into sentence case? Have you been successful doing that? I am sure you could not find what you were looking for.
Unfortunately, there is no such built in worksheet function to change case in excel to “Sentence case”. As you might already familiar with Microsoft word, the sentence case function supposed to capitalize first letter of a sentence. Why Microsoft people did not added that feature? Is it not useful? I do not have the right answer for the first question but I know for sure that the feature is important

How to add the SENTENCE CASE FUNCTIONALITY to Excel?

Here we are going to use EXCEL USER DEFINED FUNCTION procedure to this feature to our workbook. If you are not familiar with this, do not worry. You do not need any prior knowledge to do this in addition to know how to use excel. I will explain this with simple systematic procedure.

Step 01: Open new workbook and the vba coding window

Open a new Excel Workbook–> Press [ALt+F11] keys
The command will open up the excel vba coding window (Microsoft visual basic for applications)

Step 02 : Add new empty module to the vba project

Click insert -> module to add a new empty module. You will be presented automatically with the code window for your newly created module.

Step 03 : Write the given vba code in the module window

As you are here not to take vba lessons, please feel free to just copy and paste the code. It will create new vba macro to your workbook adding sentence case function called “sCase ()”. Now you can click save. Please make you select the “Excel macro-enabled Workbook (*.xlsm) ” as your file type in the “Save as” dialog box. Otherwise our precious code will not work.

Public Function sCase(str)
' Macro to make change text whithin the active cell to sentence case
b = LCase(str)
Words = Split(b)
        Words(0) = Application.WorksheetFunction.Proper(Words(0))
n_words = UBound(Words) - LBound(Words)
If (n_words > -1) Then
    For Count = 0 To n_words
    sCase = sCase + " " + Words(Count)
    Next Count
Else
    MsgBox "Please select a cell containing a string"
    Exit Function
End If
sCase = Application.WorksheetFunction.Trim(sCase)
End Function

Step 04 : Use your newly created user defined function to change texts to Sentence case

Now that you are finished, doing all the hard work it is time to test how it works. In your usual Workbook window,
Select Formulas –> Insert Function.

The Insert Function dialog box will show up. In the dialog box, you can see the “Select category” drop-down list. Scroll down to “user defined” category and select it.

Now you should be able to see our custom-made function “sCase” within the list. Select it and click “OK”.

With the popped up function argument dialog box, you can select a cell with a string to be converted to the Sentence case. Alternatively, you can use it as other common functions by typing directly within a selected cell to store new string. Function argument should be the cell, which contain the sentence to be case changed.

With the popped up function argument dialog box, you can select a cell with a string to be converted to the Sentence case. Alternatively, you can use it as other common functions simply as you used UPPER(),LOWER() or PROPER () function.

Feel free download the sample excel file and free to test the function.Make sure you enable macro when it asked to do so.Otherwise, you will not be able to use the function. Check out here to learn how to make these user defined functions available throughout all your excel workbooks by using excel add-ins feature.

Congratulation! Now you have extend Excel by one more feature. This might be very handy tool if you are working with lots of text data.
Although these functions are very useful, sometimes it is a pain how we have to achieve these simple tasks in excel. Don’t you like to do this case changes to a multiple selected cells at once or by a simple shortcut key. Moreover, you might need to know how to make these custom user defined function make available to all of your workbooks.
I will explain how to do all of these in my next tutorials. Subscribe and notified as soon as they are available.
And do not forget to share this with your friends.