Excel-VBA Solutions

Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Upper Case, Lower Case and Proper Case

In this post I will show you how to use VBA functions "UCase", "LCase" and worksheet function Proper to format the text in excel sheet. I will explain this using below example. Assume we have list of names like this.


So we have 10 names in sheet 1. As you can see that names are not in a proper format. So now let's look at how to convert this 10 names to upper case first. You can use below code to do that.

Sub ConvertToUpperCase()

Dim WS_Input As Worksheet
Dim WS_Result As Worksheet

Dim i As Integer

Set WS_Input = Worksheets("Sheet1")
Set WS_Result = Worksheets.Add

For i = 1 To 10
    WS_Result.Range("A" & i).Value = UCase(WS_Input.Range("A" & i).Value)
Next i

End Sub

So this will convert the names to upper case and list them in a new sheet. So this is the result you will get after running the code.

You can see that all the letters have converted to upper case. So now let's look at how to convert that names to lower case.  Below code will do that.


Sub ConvertToLowerCase()

Dim WS_Input As Worksheet
Dim WS_Result As Worksheet

Dim i As Integer

Set WS_Input = Worksheets("Sheet1")
Set WS_Result = Worksheets.Add

For i = 1 To 10
    WS_Result.Range("A" & i).Value = LCase(WS_Input.Range("A" & i).Value)
Next i

End Sub

You can change the limits of for next loop to do this for any amount of rows. Also you can modify the code to replace existing text in sheet 1 with result text. After running this macro result will look like this.

So now let's look at how to convert these names to proper case or title case. This is little different than other two. Because in earlier two cases, we used VBA functions. But this time we need to use a worksheet function. Because of that Proper(WS_Input.Range("A" & i).Value) will not work this time. Instead we have to use Application.WorksheetFunction.Proper(WS_Input.Range("A" & i).Value). Here is the full code.

Sub ConvertToProperCase()

Dim WS_Input As Worksheet
Dim WS_Result As Worksheet

Dim i As Integer

Set WS_Input = Worksheets("Sheet1")
Set WS_Result = Worksheets.Add

For i = 1 To 10
    WS_Result.Range("A" & i).Value = Application.WorksheetFunction.Proper(WS_Input.Range("A" & i).Value)
Next i

End Sub

You will get below result after running this macro.

So modify above codes to suit with your requirements and improve your workbooks.

Contact Form

Name

Email *

Message *