Change the case of text in excel | Using Formula | Using Macro | Skills4All

Hi guys, today we will learn how we can change the case of text in excel sheet.

Here we will discuss two methods to change the case of text:

1. by using formula and 2. by using macros. 

First we can add formula to our Excel sheet to change case of text. To change the text to uppercase, we will use the upper function =UPPER(cell ref) and will give the reference of the cell in argument to whom we want to change the case. 

Secondly, if you want to change the case of your text to lowercase then use the formula lower =LOWER(cell ref) and give the reference of cell as an argument. 

Similarly if you want to change your text to proper case, means only 1st letter will be capitalized, just use proper formula =PROPER(cell ref) and pass the cell reference as an argument. 

And now you can see the case will be changed as you will press enter. you can use fill handle to copy the formula in lower cells as well.

Important thing is that if you remove the data from the cells that you have given as an argument then the function will give you error and no content will be displayed. we need to avoid this problem, you can just copy that column where you have applied the formula of change case. Now in other column you need to paste value and here you can see, even if you delete the source cells data, the destination data will still exist. 

That's it! You have done. thumbs up:-) 

Now we come to the second method, use of macros. 

What Is a Macro in Excel?

An Excel macro is an action or a set of actions that can be recorded, named, saved and executed as many times as required and whenever desired. By using macros, we are able to automate repetitive tasks associated with data manipulation and data reporting that must be accomplished repeatedly.

This one is the best and the permanent solution for changing text in excel. 

Now what we will do? We have a macro code to change the case of text in excel. Now just copy it, go to view tab and then macros. 

Macro code to change the case of text in excel:

Sub ChangeTextCase()

Dim strChange As String
strChange = InputBox("Type l, u, or p to change case:" & vbCr & vbLf & vbCr & vbLf & _
"Lowercase = l, uppercase = u, proper case = p", "Change case of selected cells", "")
Select Case strChange
Case "l", "L"
For Each x In Application.Selection
x.Value = LCase(x.Value)
Next
Case "u", "U"
For Each x In Application.Selection
x.Value = UCase(x.Value)
Next
Case "p", "P"
For Each x In Application.Selection
x.Value = WorksheetFunction.Proper(x.Value)
Next
Case Else
MsgBox "Type l, u, or p." & vbCr & vbLf & vbCr & vbLf & "Click OK to start over."
End Select
End Sub

---------------------------------------------------------------------------------------------------------------------------

Here you need to enter the name of your macro and then click create. A visual basic module file will be opened. Just remove the existing code paste your copied code and then close the file. Now select the cells where you want to change the case. Now I again go to view then go to macros select the Macro you have created for change case, click on Run. U for uppercase, letter l lower case, and p for proper case. Yes you have done. It's working fine in this Excel sheet. 

watch video to see the whole process.

If you have learnt something from this video just like the video, subscribe our channel, & click on Bell icon so you are notified for any upcoming latest videos. 

Thank you for watching.

Now if you want to learn how to add a change case menu or tab in your Excel ribbon, so you can add this macro in all of your Excel sheets, just watch the next video how to add a new tab of change case in Excel.