Comments on: 4 ways for changing case in Excel

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. Continue reading →

Comments page 5. Total comments: 164

  1. Thanks a lot, it's indeed helpful

  2. Proper is not working for me.

  3. Thank you very much for helping me and saving my time.

  4. 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. :)

  5. 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

    1. 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

  6. 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.

  7. Thank you very much! Its save a lot of time for me

  8. wonderful, this methods are simply
    extraodinary, it have saved me lots and lots of stress keep it up.

  9. Thank's for given me lot of Excel information.

  10. 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.

    1. 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.

  11. 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

  12. Thank you for share this valuable information to us

  13. Thank you so much. What a time saver.

  14. hank you so much, for excel tips.

  15. Thank you so much, for excel tips.

  16. Thank you very much.

  17. 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!

    1. shift+F3 its work only in ms word. and i want shortcut key for excel.

  18. Such a helpful article thanks a lot

  19. Thank you so Much, its very supportive

  20. Good solution.

  21. Shift+f3 change case

  22. 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.

  23. Thank you for instruction.....

  24. I just pop in to say thank you. It really help.

    Thank you once again.

    I wish you a place in Microsoft Office headquater.

  25. i need convert the lower case into upper case for selected colums how it possible

  26. How can I enter this an an Excel cell?

    PTmixcase

    It always changes the capital T to lower case....

  27. thank you so much. it's helpful.

  28. 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.

  29. 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

    1. Hi Carl,

      Use the Text to columns option in the "Data" tab

  30. 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

  31. SUPERB, WELL DONE ,AWESOME

  32. 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.

  33. The given formula for change upper case work out only in column.
    Can you please tell how to covert upper case of rows?

  34. 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.

  35. Thank you. This was very helpful and saved lots of time.

  36. Great article Ekatrina. Thank you for taking the time to put this together. You rock!

  37. FANTASTIC , IT IS VERY USEFUL TO ME

  38. how to convert the ()sentence after writing in a paragarph

  39. Hi,
    Anybody can tell me the short cut key of UPPERCASE in excel 2007.

    Thanks.

  40. Nice article. It really helped me, thanks a lot :)

  41. excellent article. very very easy & comprehensive article. thanks a lot for educating me. warm regards.

  42. excel formula change sentence case shortcut key

  43. thanks a lot.

  44. 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.

    1. Hi Karthick,

      Did you try the "Proper" function? It works with underscores and other special characters as well

  45. I want example RamSingh, please confirm formula how to change Ram Singh.

    RamSinghVasisht Ram Singh Vasisht.

  46. Excellent solution.

    Thanks a lot

  47. 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

  48. PLZ GIVE ALL SOLUTION

  49. 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.

  50. THANK U MY PROBLAM SOLVED DUE TO WORD OPTION

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)