Changing Text Case

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.

Posted in Uncategorized

3 thoughts on “Changing Text Case

  1. 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.

  2. 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


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.