Changing Case Using Worksheet Functions and VBA

Changing Case using Worksheet Functions and VBA

Excel provides the text worksheet functions, namely the Upper Function, the Lower Function and the Proper Function, which can change the case of a specified input text string. This text string could be entered directly in the formula or entered as a cell reference.

The Upper Function converts all text in a given string to capital letters, whereas the Lower Function converts all text in a given string to lower case. The Proper Function converts the first letter of every word in a text string to a capital letter and keeps the rest of the word in lower case.

VBA also has an equivalent built-in UCase and LCase Function. The UCase Function in VBA converts all letters in a given text string to capital letters whereas the LCase Function converts all letters in a given text string to lower case. VBA does not have an equivalent Proper Function, therefore one can either use the StrConv function or access the worksheet Proper Function in VBA in order to convert a text string to proper case.

So, let’s get started with a few simple examples to illustrate how to use the above listed functions.

Introduction

Using the Upper Function

We are going to utilize the Upper Function in order to capitalize a text string. The source data is shown below and is the first worksheet in the workbook.

1) So, in cell A6 we enter the following formula:

=UPPER(A5)

2) Upon pressing CTRL-ENTER, the input text string is converted to THIS IS SOME SAMPLE TEXT as shown below.

3) We now want to capitalize some source text, using the Upper Function again, but instead of using a cell reference we will enter the text string directly in the formula. So in cell A8, enter the following formula and remember when entering text directly into a formula, always surround the text with quotation marks:

=UPPER("This Is Some Sample Text")

4) Upon pressing CTRL-ENTER the capitalized text string, THIS IS SOME SAMPLE TEXT is delivered in cell A8.

Using the Lower Function

We are going to utilize the Lower Function in order to change a text string entirely into lower case. The source data is shown below and is the second sheet in the workbook.

1) So, in cell A6 we enter the following formula:

=LOWER(A5)

2) Upon pressing CTRL-ENTER, the input text string is converted to this is some sample text as shown below.

3) We now want to convert some source text to lower case, using the Lower Function again, but instead of using a cell reference we will enter the text string directly in the formula. So in cell A8, enter the following formula and remember as mentioned before when entering text directly into a formula always surround the text with quotation marks:

=LOWER("This Is Some Sample Text")

4) Upon pressing CTRL-ENTER the text string in lower case is returned, and thus this is some sample text, is delivered in cell A8.

Using the Proper Function

We are going to utilize the Proper Function in order to put a text string into proper case. The source data is shown below and is the third worksheet in the workbook.

1) So, in cell A6 we enter the following formula:

=PROPER(A5)

2) Upon pressing CTRL-ENTER, the input text string is converted to This Is Some Sample Text as shown below.

3) We now want to convert some source text to proper case, using the Proper Function again, but instead of using a cell reference we will enter the text string directly in the formula. So in cell A8, enter the following formula:

=PROPER("this is some sample text")

4) Upon pressing CTRL-ENTER the text string in proper case is returned, and thus This Is Some Sample Text, is delivered in cell A8.

Using the UCase Function in VBA

We are going to utilize the UCase Function in order to change a text string entirely into upper case in VBA. The source data is shown below and is the fourth worksheet in the workbook.

1) So first things first, we are going to add a button to the worksheet, so go to Developer>Controls>Insert and under ActiveX Controls, select command button.

2) Draw a command button on the worksheet as shown below.

3) With Design mode and the button selected, select Properties and change the name of the button to cmdupperCase and the caption of the button to Convert to Upper Case as shown below.

4) Right-click on the button and choose View Code.

5) Enter the following code for the button click event.

Private Sub cmdupperCase_Click()

Dim inputString As String

Dim resultant As String

Let inputString = Range("A5").Value

resultant = UCase(inputString)

Range("A8").Value = resultant

End Sub

This code has two variables of the string data type. The first variable is sourced from the text in cell A5, the second variable takes its value from the UCase function converting the first variable into upper case. The last portion of the code is stating thesecond variable i.e the text in upper case, should be shown in cell A8.

6) Return to the worksheet and making sure Design mode is not selected, click on the button and the text in upper case is shown in cell A8.

Using the LCase Function in VBA

We are going to utilize the LCase Function in order to change a text string entirely into lower case, in VBA. The source data is shown below and is the fifth worksheet in the workbook.

1) So first things first, we are going to add a button to the worksheet, so go to Developer>Controls>Insert and under ActiveX Control select command button.

2) Draw a command button on the worksheet as shown below.

3) With Design mode and the button selected, select Properties and change the name of the button to cmdlowerCase and the caption of the button to Convert to Lower Case as shown below.

4) Right-click on the button and choose View Code.

5) Enter the following code for the button click event.

Private Sub cmdlowerCase_Click()

Dim rng As Range

Dim resultant As String

Set rng = Application.InputBox(Prompt:="Select the text you'd like to convert to lower case", _ Title:= "Lower Case Conversion", Type:=8)

resultant = LCase(rng)

Range("A8").Value = resultant

End Sub

This code has two variables one of the range data type and one of the string data type. The first variable is sourced using an input box and the user selects via the input box, the preferred range, the second variable takes is value from the Lcase Function converting the first variable into lower case. The last portion of the code is stating the second variable i.e the text in lower case should be shown in cell A8.

6) Return to the worksheet and making sure Design mode is not selected, click on the button. Once clicked, an input box should appear asking you to select the text that you would like to convert to lower case.

7) In this case select cell A5, however you could select any other cell containing text you’d like to convert to lower case, if the other cell contained text, using this input box.

8) Click Ok and the text converted to lower case should appear in cell A8.

Converting to Proper Case using VBA

There is no built-in Proper Case Function in Excel VBA like LCase and UCase as mentioned before. Therefore to convert a text string to proper case one either has to use the StrConv Function or access the worksheet Proper Function in VBA. We are going to look at both ways. The StrConv function can also be used to convert a string to either upper case or lower case depending on the parameter specified.

Using the StrConv function

The sample data is shown below and is the sixth worksheet in the workbook.

1) So first things first, we are going to add a button to the worksheet, so go to Developer>Controls>Insert and under ActiveX Controls select command button.

2) Draw a command button on the worksheet.

3) With Design mode and the button selected, select Properties and change the name of the button to cmdproperCase and the caption of the button to Convert to Proper Case as shown below.

4) Right-click on the button and choose View Code.

5) Enter the following code for the button click event.

Private Sub cmdproperCase_Click()

Dim inputString As String

Dim resultant As String

Let inputString = Range("A5").Value

resultant = StrConv(inputString, vbProperCase)

Range("A8").Value = resultant

End Sub

This code has two variables of the string data type. The first variable is sourced from the text in cell A5, the second variable takes its value from the StrConv function converting the first variable into proper case. If one specified vbUpperCase instead, this would convert the string to upper case, and if one specified vbLowerCase, this would convert the string to lower case. One could also convert the string to Unicode using the vbUnicode parameter in conjunction with the StrConv function or alternatively convert the string back from Unicode to the default code page of the system at hand using the vbFromUnicode parameter.

The last portion of the code is stating second variable i.e the text in proper case, should be shown in cell A8.

6) Return to the worksheet and making sure Design mode is not selected, click on the button and the text in proper case is shown in cell A8.

Using the Proper Worksheet Function in VBA

One can access the worksheet Proper function in VBA since there is no VBA equivalent. For functions that have a VBA built-in equivalent one must use the VBA equivalent instead. So still on the same sheet we were using before, we will now use the Proper worksheet Function in VBA in order to convert a text string to proper case.

1) First things first, go to Developer>Controls>Insert and under ActiveX Controls choose Check Box.

2) Insert a Check Box on the worksheet as shown below.

3) With Design mode still selected, and the Check Box selected using the Properties Window, change the name of the Check Box to chkOne and the Caption to Change Case as shown below.

4) Close the Properties Window and right-click the Check Box and choose View Code.

5) Enter the following code for the click event:

Private Sub chkOne_Click()

Dim inputstringOne As String

Dim resultantOne As String

If chkOne.Value = True Then

Let inputstringOne = Range("A5").Value

resultantOne = Application.WorksheetFunction.Proper(inputstringOne)

Range("B8").Value = resultantOne

End If

If chkOne.Value = False Then

Range("B8").Value = ""

End If

End Sub

What this code does is declare two variables both of the string data type. Using an If statement we check if the Check Box is ticked (i.e its value is true) then the first variable gets its value from whatever text is in cell A5. The second variable gets its value from the first variable which using the Proper Worksheet Function has been converted to proper case. The second variable is then input in cell B8. Then using another If statement we check if the check box is not checked then cell B8 is cleared of any data. So this creates a kind of toggle on/toggle off effect in a way.

6) With Design mode deselected check the Check Box as shown and This Is Some Sample Text is delivered in cell B8.

7) If you now deselect the Check Box, the cell B8 is cleared as was specified in the code.

Crossover Tips

Word also provides ways to change case, we are first going to look at the simple way of changing case in Word using the Ribbon and then we are going to use VBA to accomplish the same thing. Our sample document is shown below.

1) First things first, select the text and then go to Home>Font>Change Case and select an option from there, in this case we are going to select UPPERCASE as shown below. However we can using the Change Case option in the Font group, convert a text to lower case, proper case(the Capitalize Each Word) option or sentence case (where only the first letter of a sentence is capitalized).

2) The selected text should now be in upper case entirely as shown below.

Changing Case Using VBA

We are now going to look at how to change the case using VBA, make sure you save documents with VBA as macro-enabled documents. Our sample macro-enabled document is shown below.

1) First things first, go to Developer>Controls>Legacy Tools and under the ActiveX Controls choose Command Button as shown below.

2) The button by default should be inserted where the cursor is as shown below.

3) Right-click the button and select Format Control.

4) In the Format Object Dialog Box, select the Layout tab as shown below.

5) Select Advanced………..

6) In the Layout Dialog Box, select the Text Wrapping Tab as shown below and as the Wrapping Style choose Square.

7) Click Ok and Ok again and you should now have more flexibility with respect to positioning the button thus move the button slightly lower.

8) With the button selected, choose Properties and change the name of the button to cmdLower, the Caption to Lower Case and the width to 80.

9) Close the Properties Window and right-click the button and select View Code.

10) Enter the following code for the button click event:

Private Sub cmdLower_Click()

Dim firstparagraph As Range

Set firstparagraph = ActiveDocument.Paragraphs(1).Range

With firstparagraph

.Case = wdLowerCase

End With

End Sub

This code first declares a variable called firstparagraph as a range object. The difference between a range in Word and a range in Excel is that a range in Word has a set starting point and ending point that has to be specified. A paragraph in word is a well defined section that has a beginning and an end. In this case we are setting our range as the first paragraph in the document using the range method. We are then using a With End With structure, and changing the case of the paragraph.

11) Return back to the document and making sure Design mode is deselected, click on the button, we don’t in this case have to select the paragraph first since we are not using a Selection object in the code and we have already specified using the range object that our first paragraph is the segment of interest.

12) The text is now converted to lower case as shown below.

13) Now on the same document, go to Developer>Controls>Legacy Tools and insert another button in the document as shown below.

14) With the second button selected, right-click the button and choose Format Control. In the Format Object Dialog Box, select the Layout Tab and change the Wrapping Style to Square and click Ok.

15) Move the button down slightly as shown below.

16) With the second button selected and using the Properties Window, change the name of the button to cmdUpper, the caption to Upper Case and the width to 80.

17) Right-click the second button and choose View Code.

18) Enter the following code for the button click event.

Private Sub cmdUpper_Click()

Dim firstparagraph As Range

Set firstparagraph = ActiveDocument.Paragraphs(1).Range

With firstparagraph

.Case = wdUpperCase

End With

End Sub

19) Return to the document and making sure Design mode is not selected, click on the Upper Case button and the text in the first paragraph will all be converted to Upper Case as shown below.

20) Now on the same document insert another button.

21) Right-click this third button and choose Format Control, in the Format Object Dialog Box, select the Layout Tab and choose Square under Wrapping Style as for the other buttons. Click Ok.

22) Move the button until it is slightly below the second button as shown below.

23) With the third button selected, using the Properties Window change the name of the button to cmdProper, the caption to Proper Case and the width to 80.

24) Then right-click this button and for the button click event use the following code.

Private Sub cmdProper_Click()

Dim firstparagraph As Range

Set firstparagraph = ActiveDocument.Paragraphs(1).Range

With firstparagraph

.Case = wdTitleWord

End With

End Sub

25) Return back to the document, making sure Design mode is not selected and click on the Proper Case button and now the entire text in the first paragraph is converted to proper case.

And there you have it.

Conclusion

Excel provides worksheet functions to change the case of text provided, there are also VBA built-in functions that deal with changing case. Word also provides multiple options on the Ribbon that deals with changing text and one can in VBA also change the case of a text or paragraph.

Please feel free to comment and tell us if you utilize these functions often in your worksheets, documents or code.

Useful Links:

The LCASE Function

The UCASE Function

The StrConv Function