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’t eat too much turkey, did you? As for the Scripting Guys, we all – 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’s past 9:00 AM as we write this column the skies aren’t grey; the skies are flat-out black. It’s dark, it’s cold (maybe 55 degrees, tops) and while it’s not raining at the moment, it will be soon. Ah, yes: Paris in the springtime, and Seattle in the fall.

Actually, and in all fairness, September is usually our best 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’s game, the Scripting Fan not only wore a coat, but took along a pair of gloves as well, just in case.

But are we going to sit here all day whining and complaining about the weather? Well, we’d 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:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1,1) = “aBcDeF”
objWorksheet.Cells(1,2) = “ghHiJk”
objWorksheet.Cells(1,3) = “lMnOpQ”
objWorksheet.Cells(1,4) = “rStUvW”
Set objRange = objWorksheet.UsedRange
For Each objCell in objRange
    strValue = LCase(objCell.Value)
    intLength = Len(strValue)
    strleft = UCase(Left(strValue, 1))
    strRight = Right(strValue, intLength – 1)
    strValue = strleft & strRight
    objCell.Value = strValue
Next

And sure, we’ll explain how this works. After all, it’s not like we’ll be going outside to enjoy the sunshine, at least not until next July or so.

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 object and then setting the Visible property to True; that gives us a running instance of Excel that we can see onscreen. We use the Workbooks.Add() 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:

Set objWorksheet = objWorkbook.Worksheets(1)

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– some uppercase, some lowercase – into four of the cells in our worksheet:

objWorksheet.Cells(1,1) = “aBcDeF”
objWorksheet.Cells(1,2) = “ghHiJk”
objWorksheet.Cells(1,3) = “lMnOpQ”
objWorksheet.Cells(1,4) = “rStUvW”

In turn, that gives us a worksheet that looks like this:

Well, we suppose it’s kind of cute. But it’s not what we had in mind.

So maybe we should see what we can do to change this into the kind of spreadsheet that we did have in mind. To begin with, we use the UsedRange property to create a range object that encompasses all the cells in the spreadsheet that we’ve “used;” in this example, that’s cells A1 through D1. And that’s 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 D10. (In other words, all the “intermediary” cells – B5, C7, etc. – 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’ll select cells A1 through D10. The UsedRange property performs the exact same feat.

So why do we even care 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’s 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.

Which brings up another question: now that we have this collection of individual cells what do we do with it? This:

For Each objCell in objRange
    strValue = LCase(objCell.Value)
    intLength = Len(strValue)
    strLeft = UCase(Left(strValue, 1))
    strRight = Right(strValue, intLength – 1)
    strValue = strLeft & strRight
    objCell.Value = strValue
Next

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 of the cell, use the LCase function to convert all the letters to lowercase, and then assign the value to a variable named strValue:

strValue = LCase(objCell.Value)

What does that mean? That means that, on the first go-round, strValue will be equal to this:

abcdef

Which isn’t quite what we want, either. But we’re getting closer.

Our next step is to use the Len function to determine the number of characters in the variable strValue. Why do we do that? In a moment we’re going to divide our string into two pieces: the first character, which we’ll convert to uppercase, and all the remaining characters, which we’ll 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 has 6 characters, a value that gets assigned to the variable intLength:

intLength = Len(strValue)

That brings us to this fancy-looking line of code:

strLeft = UCase(Left(strValue, 1))

What we’re doing here, for starters, is using the Left function to grab the very first character in the string (the letter a); that’s what this portion of the code does:

Left(strValue, 1)

As soon as we’ve latched on to that character we use the UCase function to convert the value to uppercase; that value – A – is then assigned to the variable strLeft.

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:

strRight = Right(strValue, intLength – 1)

Here we use the Right function to grab characters from the end 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 – 1 characters. In this case, that means 6 – 1, or 5; thus we take the last five characters of the string, which just happen to be bcdef. Those 5 characters are then assigned to the variable strRight.

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:

strValue = strLeft & strRight

That makes strValue equal to Abcdef, which is exactly what we want it to be equal to. With that in mind, we next set the value of the cell to the value of strValue:

objCell.Value = strValue

And then we loop around and repeat the process with the next cell in the collection.

Will this work? Let’s put it this way: does it ever rain in Seattle? Here’s what our finished spreadsheet looks like:

That should do it, DW. As for everyone else, all we can say is this: Happy New Year!