4 ways for changing case in Excel 2016, 2013, 2010
In this article I'd like to tell you about different ways to change Excel uppercase to lowercase or proper case. You'll learn how to perform these tasks with the help of Excel lower/upper functions, VBA macros, Microsoft Word, and an easy-to-use add-in by Ablebits.
The problem is that Excel doesn't have a special option for changing text case in worksheets. I don't know why Microsoft provided Word with such a powerful feature and didn't add it to Excel. It would really make spreadsheets tasks easier for many users. But you shouldn't rush into retyping all text data in your table. Fortunately, there are some good tricks to convert the text values in cells to uppercase, proper or lowercase. Let me share them with you.
Excel functions for changing text case
Microsoft Excel has three special functions that you can use to change the case of text. They are UPPER, LOWER and PROPER. The upper() function allows you to convert all lowercase letters in a text string to uppercase. The lower() function helps to exclude capital letters from text. The proper() function makes the first letter of each word capitalized and leaves the other letters lowercase (Proper Case).
All three of these options work on the same principle, so I'll show you how to use one of them. Let's take the Excel uppercase function as an example.
Enter an Excel formula
- Insert a new (helper) column next to the one that contains the text you want to convert.
Note: This step is optional. If your table is not large, you can just use any adjacent blank column.
- Enter the equal sign (=) and function name (UPPER) in the adjacent cell of the new column (B3).
- Type in the appropriate cell reference in the parentheses (C3) after the function name.
Your formula should look like this
=UPPER(C3), where C3 is the cell in the original column that has the text for conversion.
- Click Enter.
As you can see in the screenshot above, cell B3 contains the uppercase version of the text from cell C3.
Copy a formula down a column
Now you need to copy the formula to other cells in the helper column.
- Select the cell that includes the formula.
- Move your mouse cursor to the small square (fill handle) in the lower-right corner of the selected cell until you see a small cross.
- Hold the mouse button and drag the formula down over the cells where you want it to apply.
- Release the mouse button.
Note: If you need to fill the new column down to the end of the table, you can skip steps 5-7 and just double-click on the fill handle.
Remove a helper column
So you have two columns with the same text data, but in different case. I suppose you'd like to leave only the correct one. Let's copy the values from the helper column and then get rid of it.
- Highlight the cells that contain the formula and press Ctrl + C to copy them.
- Right-click on the first cell in the original column.
- Click on the Values icon under Paste Options in the context menu.
Since you need only the text values, pick this option to avoid formula errors later.
- Right-click the selected helper column and choose the Delete option from the menu.
- Pick Entire column in the Delete dialog box and click OK.
Here you are!
This theory might look very complicated to you. Take it easy and try to go through all these steps yourself. You'll see that changing case with the use of Excel functions is not difficult at all.
Use Microsoft Word to change case in Excel
If you don't want to mess with formulas in Excel, you can use a special command for changing text case in Word. Feel free to discover how this method works.
- Select the range where you want to change case in Excel.
- Press Ctrl + C or right-click on the selection and choose the Copy option from the context menu.
- Open a new Word document.
- Press Ctrl + V or right-click on the blank page and select the Paste option from the context menu
Now you've got your Excel table in Word.
- Highlight the text in your table where you want to change the case.
- Move to the Font group on the HOME tab and click on the Change Case icon.
- Pick one of 5 case options from the drop-down list.
Note: You can also select your text and press Shift + F3 until the style you want is applied. Using the keyboard shortcut you can choose only upper, lower or sentence case.
Now you have your table with the text case converted in Word. Just copy and paste it back to Excel.
Converting text case with a VBA macro
You can also use a VBA macro for changing case in Excel 2010 / 2013. Don't worry if your knowledge of VBA leaves much to be desired. A while ago I didn't know much about it as well, but now I can share three simple macros that make Excel convert text to uppercase, proper or lowercase.
I won't labor the point and tell you how to insert and run VBA code in Excel because it was well described in one of our previous blog posts. I just want to show the macros that you can copy and paste into the code Module.
If you want to convert text to uppercase, you can use the following Excel VBA macro:
Sub Uppercase()
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = UCase(Cell.Value)
End If
Next Cell
End Sub
To apply Excel lowercase to your data, insert the code shown below into the Module window.
Sub Lowercase()
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = LCase(Cell.Value)
End If
Next Cell
End Sub
Pick the following macro if you want to convert your text values to proper / title case.
Sub Propercase()
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = _
Application _
.WorksheetFunction _
.Proper(Cell.Value)
End If
Next Cell
End Sub
Quickly change case with the Cell Cleaner add-in
Looking at the three methods described above you might still think that there is no easy way to change case in Excel. Let's see what the Cell Cleaner add-in can do to solve the problem. Probably, you'll change your mind afterwards and this method will work best for you.
- Download the add-in and install it on your computer.
After the installation the new Ablebits Data tab appears in Excel.
- Select the cells where you want to change the text case.
- Click on the Change Case icon in the Clean group on the Ablebits Data tab.
The Change case pane displays to the left of your worksheet.
- Select the case you need from the list.
- Press the Change case button to see the result.
Note: If you want to keep the original version of your table, check the Back up worksheet box.
With Cell Cleaner for Excel the changing case routine seems to be much easier, doesn't it?
Besides changing text case Cell Cleaner can help you to convert numbers in the text format to the number format, delete unwanted characters and excess spaces in your Excel table. Download the free 30-day trial version and check out how useful the add-in can be for you.
Video: how to change case in Excel
I hope now that you know nice tricks for changing case in Excel this task will never be a problem. Excel functions, Microsoft Word, VBA macros or Ablebits add-in are always there for you. You have a little left to do - just choose the tool that will work best for you.
122 Responses to "4 ways for changing case in Excel 2016, 2013, 2010"
Capitalize first letters is most useful case for me, nice article.
Thank you for your feedback, Grigory.
hellow
really help full add
thanks for the efforts to publish
easy add text as beginning
but how to add more rows ?
thanks
It is very helpful for my process but I cannot downlaod in office. Kindly provide how to create addin & how to add this to toolbar in excel 2007. I need all codings for cell cleaner.
Thanks in advance
Thank you for your feedback, Mallikharjuna. I am sorry we don't provide codes of our products. If you have problems with the installation of the add-in, please contact me at support@ablebits.com.
Hi
My requirements:
I need to change case in to Upper & Proper depend on the criteria.
I have to remove all special charecter excluding parentheses (()), ampersand (&) and hyphen(-)
English word "And" has to replace by "&".
when we do the proper case LLC and INC has to be remail same like LLC and INC only
In a sentense if it is Corp, that has to change as Corporation.
I need all the above in CellCleaner Add-in
Please do the needful & your help much appreciated.
Hi, Mallikharjuna,
Thank you for the update. I am really sorry for a delay in my reply.
Please specify the criteria for changing the case.
If you want to delete only some special symbols, click on the Remove Characters icon and select the Remove custom characters option. Just enter the symbols you want to delete.
You can easily make the necessary replacement by using the standard Excel Find and Replace feature.
Please let me know if you have any other questions.
I ended up here looking to find help in converting the first letter of the department in cell C2 to uppercase using the text formula.... Can you help me? I know to click the text formula button and to choose upper, but if I just click C2 to add it to the text box in the functions arguments dialog box it changes all the text to uppercase. please help!
Hi Crystal,
Have you tried the "Proper" function instead of upper?
AWESOME
haha copy paste from excel to word, and copied back to excel is a very good trick. thank you so much ekaterina! :D
I AM RINU.
I SOLVED MY PROBLEM.
THANKU SO MUCH
is there any shortcut key to change the case in EXCEL
Hello,
Unfortunately, there is not such a shortcut in Excel.
shift+f3
This one for MS-Word
Technicallly there is a such a shortcut. Using some formulas, you can do some basic case change. You may run into issues with hyphenated words and such.
=CONCATENATE(UPPER(LEFT(B3,1)),LOWER(RIGHT(B3,LEN(B3)-1))) is an example formula.
THANK U MY PROBLAM SOLVED DUE TO WORD OPTION
The keyboard my laptop uses upper cases only, but i would like you to help me on how i can set my keyboard to work normal.
PLZ GIVE ALL SOLUTION
Hi,
I have 2007 xl, in pdf some all the letters in caps only. But I copy the pdf to xl format change it as upper lower case only. ex: in PDF 12505 SW NORTH DAKOTA
paste in xl 12505 Sw North Dakota. How can I convert in all caps
Excellent solution.
Thanks a lot
I want example RamSingh, please confirm formula how to change Ram Singh.
RamSinghVasisht Ram Singh Vasisht.
I am looking for an option to do this:
EMPLOLYEE_IDENTIFIER_DETAILS - Employee_Identifier_Details
Basically after each underscore (_), the first letter only need INIT CAPS. Could you please help me on this.
BTW, your article is excellent and down to earth. You helped lot of our people time and manual effort. We are grateful for that service.
Hi Karthick,
Did you try the "Proper" function? It works with underscores and other special characters as well
thanks a lot.
excel formula change sentence case shortcut key
excellent article. very very easy & comprehensive article. thanks a lot for educating me. warm regards.
Nice article. It really helped me, thanks a lot :)
Hi,
Anybody can tell me the short cut key of UPPERCASE in excel 2007.
Thanks.
how to convert the ()sentence after writing in a paragarph
FANTASTIC , IT IS VERY USEFUL TO ME
Great article Ekatrina. Thank you for taking the time to put this together. You rock!
Thank you. This was very helpful and saved lots of time.
Thank you so much for this article. I wish I'd read it years earlier! I used =PROPER(C3) to convert my data and will use it often in the future. Thanks again.
The given formula for change upper case work out only in column.
Can you please tell how to covert upper case of rows?
Requested Sir/Mam,
I found a short key in excel to upper case to lower case and lower case to upper case.
Please reply me.
SUPERB, WELL DONE ,AWESOME
Hi Good morning. I am from Bangladesh. Special Thanks for your good tips. This is really helpful tropics for us. Thanks, M.M. Feroj Hossain
is there a way to divide the cell that has two names into two cell to have first name and last name in each? ie {john doe} = {john} {doe}
thanks
Hi Carl,
Use the Text to columns option in the "Data" tab
Hi ,
After i convert all the letters to uppercase I am unable to delete the original column with lower case.
How to go about it ??
The moment i delete the old one even the new one goes.
thank you so much. it's helpful.
How can I enter this an an Excel cell?
PTmixcase
It always changes the capital T to lower case....
i need convert the lower case into upper case for selected colums how it possible
I just pop in to say thank you. It really help.
Thank you once again.
I wish you a place in Microsoft Office headquater.
Thank you for instruction.....
hello,
I have Excel 2010 &
I want to Upper case only first three characters of my data,how can it be done using Upper function?
Thanks in advance.
Shift+f3 change case
Good solution.
Thank you so Much, its very supportive
Such a helpful article thanks a lot
Hi, hope you can reply soon. is there a way to uppercase the letters in a text box in excel ? When I record macro and highlight the text in the text box then I press shift+F3 it changes but when i apply the macro, nothing happens. Thank You!
shift+F3 its work only in ms word. and i want shortcut key for excel.
Thank you very much.
Thank you so much, for excel tips.
hank you so much, for excel tips.
Thank you so much. What a time saver.
Thank you for share this valuable information to us
I was reading through your site and found that you give very good advice, so I wanted to see if you could help me with a minor Excel 2013 issue...
I use Excel 2013 and would like to force an input to a cell as a capital letter. For example, if someone were to put a small x in the box, I want it converted automatically to a capital X as soon as they exit that cell. I do not want it applied globally, only to certain cells within a worksheet. I'd rather not use a macro for this as many people disable macros as security risks and the "Upper" function only mirrors the contents of one cell into another cell in capital letters.
Can you help?
Thank you,
Chris
PETER SMITH = Peter Smith
JOHN MCTEAR = John Mctear
But I need a correction to read
JOHN MCTEAR = John McTear
Excellent article but how would you solve the above.
I don't think there is any way to solve the issue. This is something that needs (extremely basic) AI logic. For example, if "Mc" is the only case where this applies, you can add a formula to catch that. Its complicated, but the logic is - 1. Find the position of the text "Mc" ---- 2. Change to proper case the text until "Mc" ---- 3. Change to proper case the rest of the characters.
In your case, the position of Mc is 7 (step 1). So characters until Mc (7+1) will become John Mc when using proper case (step 2). then the characters after that when changed to proper case will become "Tear" (step 3) - Combine all three, you get John McTear.
See the below formula - the "Find" function returns an error if the text (Mc) is not present. Hence the entire formula is packed inside an iferror function. Obviously, A1 is the cell where the original name is.
=IFERROR(LEFT(PROPER(A1),FIND("MC",UPPER(A1))+1)&PROPER(RIGHT(A1,LEN(A1)-FIND("MC",UPPER(A1))-1)),PROPER(A1))
Hope this helps.
Thank's for given me lot of Excel information.
wonderful, this methods are simply
extraodinary, it have saved me lots and lots of stress keep it up.
Thank you very much! Its save a lot of time for me
This is a very quick guide and was very helpful.. the short cut (Shift + F3) didn't work for me.. i use Excel 2010. i had to make use of the formular.
thanks.
Hi Sir/Madam
I want to know formula in excel for make short letter from sentence.
Example A1= Roumdul White Rice, the result =RWR
Please help me as soon as possible.
thank
Hi Sam,
the logic for your case is to find all the "spaces" and then convert the next character to uppercase and add them all into one string. There are a couple of challenges with this. First, how many spaces will be there in each of the text? Excel does not have a looping function to keep running for several times. So the number of spaces should be definable. Second, there should not be any cases where you would want a different logic of shortening. For example, if there are two cases where the characters are same RWR, you might want to adjust one of them to differentiate. This would call for AI.
The second issue is hard to crack. But for the first one, we can solve it using the Text to column function to split the text at each space. Based on the number of spaces, your text would be sitting in multiple columns and you can enter the formula in an empty column to add all the first characters of the different columns.
Taking the example you have provided, there would be three columns - A, B, C - where the text would be. Your formula will be in column D as "=upper(left(A1,1))&upper(left(B1,1))&upper(left(C1,1))"
If your text has 4 words (4 spaces), it will be spread in 4 columns (A, B, C, D) and you have to add one more &upper(left(D1,1)) to the formula. The good thing about this formula is that it will work with text that has lesser words as well.
Hope this helps
Thanks you saved my lot of times to change in uppercase. I'm working on copy paste work. & your article is good for me thanks again. :)
Thank you very much for helping me and saving my time.
Proper is not working for me.
Thanks a lot, it's indeed helpful
Thanks a lot
this is very helpful, thank you.
very helpful article, thank you so much
what is shortcut key of upper and lower and proper case
Thanks for much for this article. It was informative, detailed and quite helpful.
Requested Sir/Mam,I found a short key in excel to upper case to lower case and lower case to upper case.Please reply me.
how use the shortcut key for 2010 ms office means in words document to apply upper and lower case letter....
I am looking to have my WORKSHEET entirely UPPERCASE. Is there a way to do so? I realize that your short cuts all affect one or two cells/rows/columns; I need to do the entire sheet.
Thank you.
Thank you so much for this. It is informative, detailed and quite helpful. you saved alot of my time
Thanks your article helped me to avoid retyping the document.
Regards
Thank you so much for excellent solution.
Is there any way for the case to be changed AS YOU WRITE the text? I mean to be changed directly when editing the cell, not afterwards by selecting the cell(s) and applying a formula. Thank you for the answer.
Hello, Eric,
What if you simply press Caps Lock? Helps all the time!
I thought your advise would be more helpful and especially more professional. But in front of this answer I realized I made a mistake addressing my question to this site. Very good, because I intended to buy your product. And now, you just saved me 100 EUR! Your company should thank you for chasing away another client :) Keep up your good work, Natalia!!! For other people who are looking the same thing I did, here is the search string you can use in Google: "vba force proper case" (or "... sentence case", if you need). Proper case (in Excel) = Title Case (in Word). You will find the solution.
Eric,
I sincerely apologize if you were offended by my reply and if the suggested workaround is not what you were looking for. You see, from the task you described it looked like you needed an instant solution for changing the case when you type. Since there are lots of people who are new to Excel and don't know whether this or that works, this was the first obvious thing to offer.
Though I think the vba as a solution is a bit more complicated than it has to, I'm glad that you've managed to find it and shared it here in case.
My reply is ONLY for people searching same thing as me. Please DO NOT follow Natalia's opinion ("vba is a bit more complicated"). You don't have to learn VBA to be able to apply a script. You can solve it with a simple copy/paste. And using the search string i gave you, you will find complete instructions where and how to paste it. Good luck :)
It is very nice way for changing the method ......
Thanks for this.
Very helpful.
Good, you solved my problems of changing Case
Thanks
This article saved my day. Thank you
thank you. it's help me a lot
thank you. it's help me a lot. again & again thanks
Thanks sooo much to solve my problem to see your article.
This was so helpful and the detailed step by step - right on! Thank you!
VERY HELP FULL THANKS A LOT
I need to change a pasted string such as 2A3177b0Ce8a to 2A:31:77:B0:CE:8A where the incoming data is text representation of a MAC address and the displayed string is upper case text with colons in it I can paste to a text field that requires the formatting I have shown. Any ideas?
Thanks a lot, My problem Solved
Thank you soo much for this information
Very, very useful piece.
Thanks a lot!
Thank you sooooo much for this article!!!!!!!!!!! This truly makes my excel work easy and fast :-) More power to you, dear writer!
Could the macros or add-ins be modified to include "smart sentence case," excluding acronyms and abbreviations (capital letters either consecutive or separated by periods) and with the option to add exceptions for company and brand names with stylized capitalization (example: BAMTech)?
Thanks a lot. I had known how to copy to MS Word, change case and return, but doing it on my spreadsheet was just mind blowing. I have learnt something new today, thanks to you.
Thank you for the add, very useful
Good Working it is helpful for users .
Thank you.
I have download the addin, but the menu is not there
Hello, Faried,
I’m very sorry to hear that you’re having difficulties with our product.
If you have successfully installed our add-in, but don't see Ablebits tabs in your Excel Ribbon, please first make sure that the add-in isn’t disabled:
1 Go to File -> Options -> Add-ins, open the “Manage” drop-down list at the bottom of the window, select “Disabled Items” and click Go. If you see the Ablebits add-in in the list, select it and click the ‘Enable’ button.
Then make sure that the add-in loads correctly:
2 Go to File -> Options -> Add-ins again, select “COM Add-ins” from the “Manage” drop-down list and click Go. Find the Ablebits add-in in the list. If it is unchecked, check it and click OK.
If these steps don’t work for you, please contact our support service team, support@ablebits.com, for further assistance.
This article is a life-changer for me as I just started out in Admin a year ago and am still learning all the computer short-cuts to make work less tedious and repetitive.
Great job!
And, Thank you
Very very useful. i am searching since long. Thank you.
NICE EXCELLENT
Thank you so much for this article! The steps were super clear and easy to follow. And you just saved this busy art teacher hours of retyping names!!
A very helpful write up
Such BS just to get upper case changes made in Excel. Microsoft - you can do better than this. It should be in the list to add to the menu instead of making us create formulas or go to a site to DL an add on. SMH Making it too hard.
Thank you
Thanks
Absolutely very important article. The Ablebit Add in makes it so easy and simple with even more options at your disposal. Thank you very much and God bless you
I will always be grateful for this I have been having a lot of issues when it comes to changing cases in my excel data. thanks
Very useful information for changing cases in Excel. Thank you and with best wishes for you.
Thank you!!
thanks a lot
Im satisfactory to solve my problem