{"id":2372,"date":"2015-03-09T14:20:08","date_gmt":"2015-03-09T14:20:08","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/?p=2372"},"modified":"2023-12-31T00:05:24","modified_gmt":"2023-12-31T00:05:24","slug":"how-to-change-case-text-in-excel","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/how-to-change-case-text-in-excel\/","title":{"rendered":"How to change case text in Excel"},"content":{"rendered":"

Quick and easy ways to change case text in Excel.<\/strong><\/h2>\n

You may have a list which you wish to change the case test in Excel. Rather than manually re-entering the data in Excel, there are some useful functions you can call on to save you this work. Let’s look at a few of them.<\/p>\n

In this example we have a list of planets\u00a0starting in lowercase and we wish to have them starting in uppercase.\u00a0Here we will use the Proper<\/strong> function.<\/p>\n

Step 1<\/strong>\u00a0Select\u00a0the\u00a0adjacent blank cell\u00a0and type =Proper(A1)<\/p>\n

Step 2.<\/strong>\u00a0Autofill\u00a0through to B8. You should now have a list of Planets, not planets!<\/p>\n

\"Excel_proper_function\"<\/a><\/p>\n

 <\/p>\n

Step 3.<\/strong> To replace the original data copy the cells B1:B8 and paste as values back to A1:A8.<\/p>\n

Step 4.<\/strong> Finally\u00a0clear the\u00a0range B1:B8.<\/p>\n

To turn text to upper case repeat the steps above using\u00a0 the Upper<\/strong> function instead of Proper. And, yes you guessed it, use the Lower<\/strong> function to change test all to lowercase.<\/p>\n

Writing a Macro to\u00a0run the Proper case function<\/strong><\/h3>\n

If you need to change the case of text in Excel on a routine basis, you can save even more time by automating the process with a quick macro.<\/p>\n

Using a blank Workbook press Alt+F11<\/strong> to go to the VBA editor<\/strong>.<\/p>\n

\"insert_module_macro\"<\/a>
\nInsert a\u00a0 new module and type the following 2 macros.<\/p>\n

Sub Proper()
\nDim cell As Range\u00a0\u00a0 ‘cell is a name for the range variable
\nFor Each cell In Selection
\ncell = WorksheetFunction.Proper(cell)
\nNext cell
\nEnd Sub<\/p>\n

Sub Upper()
\nDim cell As Range
\nFor Each cell In Selection
\ncell = UCase(cell)
\nNext cell
\nEnd Sub<\/p>\n

Now you can close the “Microsoft Visual Basic for Applications” window (don’t worry your macros are now saved), and return to your spreadsheet.<\/p>\n

Save \u00a0your spreadsheet as a Macro-Enabled\u00a0workbook called MyMacros.<\/p>\n

You\u00a0could use the workbook to store other macros\u00a0for use in any\u00a0workbook and assign them to buttons on the Quick Access Toolbar<\/strong>.<\/p>\n

Finally\u00a0hide the workbook by selecting View, Hide<\/strong> and\u00a0exit Excel saving again\u00a0as MyMacros.<\/p>\n

When you return to\u00a0Excel open MyMacros and add the Proper and Upper macros to the Quick Access Toolbar as follows.<\/p>\n

1. Click the arrow on the right of the Quick Access Toolbar.<\/p>\n

\"more_commands_quick_access_toolbar\"<\/a><\/p>\n

2. Select More Commands<\/strong>.
\n3. Choose Macros<\/strong>\u00a0in the\u00a0commands from option.
\n4.\u00a0Click Proper and click Add, click Upper then Add.<\/strong>
\n5. Select Modify<\/strong> to change the button images.
\n6. Change the\u00a0Display name<\/strong>.
\n6. Reposition the buttons to suit.<\/p>\n

Now exit Excel again and test your macros work by selecting\u00a0some text.<\/p>\n

The hidden MyMacros workbook automatically opens when the Proper Case button is clicked and changes the text accordingly. A press of the button and the work is done, whether for a single cell or for a large range of selected cells in any workbook,\u00a0saving you time and the risk of errors from manual data entry.<\/p>\n

\"Propermac2\"<\/a><\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Quick and easy ways to change case text in Excel. You may have a list which you wish to change the case test in Excel. Rather than manually re-entering the data in Excel, there are some useful functions you can call on to save you this work. Let’s look at a few of them. In […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4,6],"tags":[88,90,344,436,445,557,614],"class_list":["post-2372","post","type-post","status-publish","format-standard","hentry","category-excel-training","category-hints-tips","tag-change-case-text-in-excel","tag-changing-lowercase-to-uppercase-in-excel","tag-lower-function-excel","tag-prevent-data-entry-errors-in-excel","tag-proper-function","tag-upper-function-excel","tag-write-a-macro-to-change-text-in-excel"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2372","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=2372"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2372\/revisions"}],"predecessor-version":[{"id":6893,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/2372\/revisions\/6893"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=2372"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=2372"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=2372"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}