A question from Los Gatos (I won’t hold it against him that he’s from a town called “The Cats”). How do you convert a whole column of text from lower case to upper case?
I don’t know any way to do that in the user interface. Scratch that, I do know one way: You can buy JWalk’s PUP5 Add-in. If you want to do it yourself, you’ll need to use VBA. This example converts all the text in A1:A12 to upper case.
Sub ConvertUpper()
Dim rcell As Range
For Each rcell In Sheet1.Range(“A1:A12”).Cells
rcell.Value = UCase(rcell.Value)
Next rcell
End Sub
To convert to lower case, use the LCase function instead of UCase. To convert to proper case, see this post.
Of course you always have the option of one-at-a-timing it with the UPPER, LOWER, and PROPER worksheet functions.
From the UI you could:
– add a column
– enter the case changing formula ‘=upper(??)’
– copy the resulting values
– paste special/values back over the original values
– delete the column you added.
Another way which doesn’t require you to specify exactly how many cells you want to do is:
Sub ConvertUpper()
dim i as integer
i=0
while range(“a1?).offset(0,i).value <> “”
range(“a1?).offset(0,i).value=UCase(range(“a1?).offset(0,i).value)
wend
End Sub
Oops, forgot an i=i+1 at the bottom of the while loop. Also the indenting didn’t work.