{"id":66423,"date":"2006-09-21T15:50:00","date_gmt":"2006-09-21T15:50:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/09\/21\/how-can-i-make-the-first-character-in-a-cell-uppercase-and-all-the-other-characters-lowercase\/"},"modified":"2006-09-21T15:50:00","modified_gmt":"2006-09-21T15:50:00","slug":"how-can-i-make-the-first-character-in-a-cell-uppercase-and-all-the-other-characters-lowercase","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-make-the-first-character-in-a-cell-uppercase-and-all-the-other-characters-lowercase\/","title":{"rendered":"How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! In an Excel worksheet, how can I make the first character in each cell uppercase and the remaining letters in each cell lowercase?

— DW<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, DW. To begin with, we hope you had a happy holiday season; you didn\u2019t eat too much turkey, did you? As for the Scripting Guys, we all \u2013 oh, sorry. Silly us; we made the mistake of looking out the window and naturally assumed that we must be in the middle of winter. Although it\u2019s past 9:00 AM as we write this column the skies aren\u2019t grey; the skies are flat-out black. It\u2019s dark, it\u2019s cold (maybe 55 degrees, tops) and while it\u2019s not raining at the moment, it will be soon. Ah, yes: Paris in the springtime, and Seattle in the fall. <\/P>\n

Actually, and in all fairness, September is usually our best<\/I> month for weather; the typical pattern around here is miserable June, mediocre July, so-so August, then a really nice, often-times hot September. In fact, as a season ticket holder for University of Washington football the Scripting Guy who writes this column has occasionally gotten a bit of a sunburn from sitting at the games, even though he spends a large portion of the summer months outdoors. For last week\u2019s game, the Scripting Fan not only wore a coat, but took along a pair of gloves as well, just in case.<\/P>\n

But are we going to sit here all day whining and complaining about the weather? Well, we\u2019d planned on it. But then we figured you might be more interested in seeing how you can make each character in each cell in an Excel worksheet uppercase and all the remaining characters in the cell lowercase. So:<\/P>

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
objWorksheet.Cells(1,1) = “aBcDeF”\nobjWorksheet.Cells(1,2) = “ghHiJk”\nobjWorksheet.Cells(1,3) = “lMnOpQ”\nobjWorksheet.Cells(1,4) = “rStUvW”\nSet objRange = objWorksheet.UsedRange<\/p>\n
For Each objCell in objRange\n    strValue = LCase(objCell.Value)\n    intLength = Len(strValue)\n    strleft = UCase(Left(strValue, 1))\n    strRight = Right(strValue, intLength – 1)\n    strValue = strleft & strRight\n    objCell.Value = strValue\nNext\n<\/PRE>\n
And sure, we\u2019ll explain how this works. After all, it\u2019s not like we\u2019ll be going outside to enjoy the sunshine, at least not until next July or so.<\/P>\n
As you can see (and as we can see, as long as we turn on the lights in the office), we start out by creating an instance of the Excel.Application<\/B> object and then setting the Visible<\/B> property to True; that gives us a running instance of Excel that we can see onscreen. We use the Workbooks.Add()<\/B> method to add a new workbook to our instance of Excel, then use this line of code to bind to the first worksheet in the workbook:<\/P>
Set objWorksheet = objWorkbook.Worksheets(1)\n<\/PRE>\n
Got that? Good. What we need next is some text that we can reformat; the following four lines of code simply type an assortment of characters\u2013 some uppercase, some lowercase \u2013 into four of the cells in our worksheet:<\/P>
objWorksheet.Cells(1,1) = “aBcDeF”\nobjWorksheet.Cells(1,2) = “ghHiJk”\nobjWorksheet.Cells(1,3) = “lMnOpQ”\nobjWorksheet.Cells(1,4) = “rStUvW”\n<\/PRE>\n
In turn, that gives us a worksheet that looks like this:<\/P>\"Microsoft \n

Well, we suppose it\u2019s kind<\/I> of cute. But it\u2019s not what we had in mind.<\/P>\n
So maybe we should see what we can do to change this into the kind of spreadsheet that we did<\/I> have in mind. To begin with, we use the UsedRange<\/B> property to create a range object that encompasses all the cells in the spreadsheet that we\u2019ve \u201cused;\u201d in this example, that\u2019s cells A1 through D1. And that\u2019s a good question: what would the UsedRange be if we typed something in cell A1 and something in cell D10? In that case, the UsedRange would consist of cells A1 through<\/I> D10. (In other words, all the \u201cintermediary\u201d cells \u2013 B5, C7, etc. \u2013 would be part of the range, even though none of these cells actually contain data.) Think of it this way: suppose you click in cell A1, then hold down the Shift key and click in cell D10. Do that, and you\u2019ll select cells A1 through D10. The UsedRange property performs the exact same feat.<\/P>\n
So why do we even care<\/I> about the UsedRange? Well, we need to loop through each cell in the worksheet and then individually adjust the letter casing in each cell; as far as we know there\u2019s no way to apply this type of formatting to the worksheet as a whole, at least not in one fell swoop. The UsedRange property gives us a quick and easy way to return a collection of individual cells.<\/P>\n
Which brings up another question: now that we have this collection of individual cells what do we do<\/I> with it? This:<\/P>
For Each objCell in objRange\n    strValue = LCase(objCell.Value)\n    intLength = Len(strValue)\n    strLeft = UCase(Left(strValue, 1))\n    strRight = Right(strValue, intLength – 1)\n    strValue = strLeft & strRight\n    objCell.Value = strValue\nNext\n<\/PRE>\n
As you can see, we set up a For Each loop to loop through all the cells in the range. Inside that loop the first thing we do is grab the Value<\/B> of the cell, use the LCase<\/B> function to convert all the letters to lowercase, and then assign the value to a variable named strValue:<\/P>
strValue = LCase(objCell.Value)\n<\/PRE>\n
What does that mean? That means that, on the first go-round, strValue will be equal to this:<\/P>
abcdef\n<\/PRE>\n
Which isn\u2019t quite what we want, either. But we\u2019re getting closer.<\/P>\n
Our next step is to use the Len<\/B> function to determine the number of characters in the variable strValue. Why do we do that? In a moment we\u2019re going to divide our string into two pieces: the first character, which we\u2019ll convert to uppercase, and all the remaining characters, which we\u2019ll leave in lowercase. In order to divide the string, and then recombine it, we need to know how many characters are in that string. The Len function tells us that the string abcdef<\/I> has 6 characters, a value that gets assigned to the variable intLength:<\/P>
intLength = Len(strValue)\n<\/PRE>\n
That brings us to this fancy-looking line of code:<\/P>
strLeft = UCase(Left(strValue, 1))\n<\/PRE>\n
What we\u2019re doing here, for starters, is using the Left<\/B> function to grab the very first character in the string (the letter a<\/I>); that\u2019s what this portion of the code does:<\/P>
Left(strValue, 1)\n<\/PRE>\n
As soon as we\u2019ve latched on to that character we use the UCase<\/B> function to convert the value to uppercase; that value \u2013 A<\/I> \u2013 is then assigned to the variable strLeft.<\/P>\n
That gives us our initial uppercase character; now we simply need to combine that initial character with the remaining characters in the string. In order to get those remaining characters we use this line of code:<\/P>
strRight = Right(strValue, intLength – 1)\n<\/PRE>\n
Here we use the Right<\/B> function to grab characters from the end<\/I> of the string strValue (as opposed to the Left function, which grabs characters from the beginning of the string). How many characters do we want to grab? Well, we want all the characters except 1 (the first one), which can only mean one thing: we want to grab intLength \u2013 1<\/B> characters. In this case, that means 6 \u2013 1, or 5; thus we take the last five characters of the string, which just happen to be bcdef<\/I>. Those 5 characters are then assigned to the variable strRight.<\/P>\n
As soon as we have both the beginning and ending portions of our string we can then combine the two using this line of code:<\/P>
strValue = strLeft & strRight\n<\/PRE>\n
That makes strValue equal to Abcdef<\/I>, which is exactly what we want<\/I> it to be equal to. With that in mind, we next set the value of the cell to the value of strValue:<\/P>
objCell.Value = strValue\n<\/PRE>\n
And then we loop around and repeat the process with the next cell in the collection.<\/P>\n
Will this work? Let\u2019s put it this way: does it ever rain in Seattle? Here\u2019s what our finished spreadsheet looks like:<\/P>\"Microsoft \n

That should do it, DW. As for everyone else, all we can say is this: Happy New Year!<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! In an Excel worksheet, how can I make the first character in each cell uppercase and the remaining letters in each cell lowercase?— DW Hey, DW. To begin with, we hope you had a happy holiday season; you didn\u2019t eat too much turkey, did you? As for the Scripting Guys, we all […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[48,49,3,5],"class_list":["post-66423","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! In an Excel worksheet, how can I make the first character in each cell uppercase and the remaining letters in each cell lowercase?— DW Hey, DW. To begin with, we hope you had a happy holiday season; you didn\u2019t eat too much turkey, did you? As for the Scripting Guys, we all […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66423","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=66423"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66423\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=66423"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66423"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66423"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}