{"id":105,"date":"2014-05-15T16:09:02","date_gmt":"2014-05-15T16:09:02","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/vbatraining\/?p=105"},"modified":"2023-12-31T00:11:04","modified_gmt":"2023-12-31T00:11:04","slug":"excel-vba-uppercase-lower-case","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/excel-vba-uppercase-lower-case\/","title":{"rendered":"Excel VBA Uppercase, Lowercase and more"},"content":{"rendered":"<p>Ever received a spreadsheet and all the data on it is in the wrong case? You might received a dodgy dump of data from your website or database, or maybe the user-inputted data is poorly or inconsistently formatted. Here I&#8217;ll guide you through how to do Excel VBA Uppercase, lowercase and capitalising the first letter of a string. All these tasks are invaluable when you have to manipulate your data using VBA.<\/p>\n<figure style=\"width: 512px\" class=\"wp-caption alignnone\"><img decoding=\"async\" class=\" \" alt=\"wrong kind of case. we want excel vba uppercase\" src=\"http:\/\/icons.iconarchive.com\/icons\/artua\/dragon-soft\/512\/Briefcase-icon.png\" width=\"512\" height=\"512\" \/><figcaption class=\"wp-caption-text\">Not this kind of case! (image copyright <a href=\"http:\/\/icons.iconarchive.com\/icons\/artua\/dragon-soft\/512\/Briefcase-icon.png\" target=\"_blank\" rel=\"noopener\">iconarchive<\/a>)<\/figcaption><\/figure>\n<h2>Excel VBA Uppercase<\/h2>\n<p>First, converting strings to upper case through Excel VBA: you need the <strong>Ucase<\/strong> function. Let&#8217;s say you want to loop through column A, replacing the current cell with an upper case version:<\/p>\n<pre>For i = 1 to cells(Rows.Count, 1)\n\n     Cells(i,1) = Ucase(Cells(i,1))\n\nNext i<\/pre>\n<p>And that&#8217;s how you do Excel VBA Uppercase.<\/p>\n<h2>Excel VBA Lowercase<\/h2>\n<p>Lower case couldn&#8217;t be simpler: the <strong>Lcase<\/strong> function. Here&#8217;s an example of converting a variable to lower case:<\/p>\n<pre>Dim empStatus As String\n\nempStatus = \"CONTRACT\"\n\nempStatus = Lcase(empStatus)\n\nMsgBox empStatus<\/pre>\n<h2>How do I capitalise the first letter in a cell (or string) ?<\/h2>\n<p>Excel VBA does not have a native way to do this; you could use some clumsy string manipulation methods but let&#8217;s lean on the Excel functionality: the Excel <strong>Proper<\/strong> function. You can summon any function from Excel using the Application.WorksheetFunction method.<\/p>\n<p>Here&#8217;s an example, capitalising the first letter of every cell in column C:<\/p>\n<pre>For i = 1 to cells(Rows.Count, 3)\n\n     Cells(i,3) = Application.WorksheetFunction.Proper(Cells(i,3))\n\nNext i<\/pre>\n<p>Three quick ways to manipulate text strings using Excel VBA,<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Ever received a spreadsheet and all the data on it is in the wrong case? You might received a dodgy dump of data from your website or database, or maybe the user-inputted data is poorly or inconsistently formatted. Here I&#8217;ll guide you through how to do Excel VBA Uppercase, lowercase and capitalising the first letter [&hellip;]<\/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,19],"tags":[],"class_list":["post-105","post","type-post","status-publish","format-standard","hentry","category-excel-training","category-vba-training"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/105","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=105"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/105\/revisions"}],"predecessor-version":[{"id":6870,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/105\/revisions\/6870"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=105"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=105"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=105"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}