Results 1 to 11 of 11

Thread: Upper Case

  1. #1
    New Lounger
    Join Date
    Sep 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Upper Case

    Is there an easy way to make all text in excel (97) into upper case?

  2. #2
    New Lounger
    Join Date
    Jan 2003
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upper Case

    Go to a blank sheet and use the "UPPER" formula in cell A1 -linking it to cell A1 on the sheet you want to convert. Autofill the entire workbook - and you will have a copy in upper case...but you're not finished yet. Copy and paste this sheet using paste special > value only > to another sheet - this will eliminate the cells being a formula and give you text only.

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upper Case

    Please, I am by no means an expert when it comes to Excel. Your directions of linking and autofilling are a little beyond what I know how to do. Could you please take it one step at a time and in a layman's (a beginner's) terms?

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upper Case

    Steve,

    A quick and nasty way is to use Word.

    .Highlight the cells in Excel
    .Copy them
    .Paste them into Word
    .Highlight the resulting table
    .Press Shift+F3 several times until the letters are all upper case
    .Copy
    .Paste back into Excel

    If you're going to do it frequently though I'd write a macro. Is this a one-off only?

    Geoff
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upper Case

    Yes, I will probably be doing this frequently. Can you write a macro that I can use? Also, if you do, I have never used any macros before so could you please explain how I go about setting up this and any other macro?

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upper Case

    Hi,

    From Excel, press Alt + F11 to get into the VB editor. Clcik on "This Workbook" in the "project explorer" window in the top left. Select Insert, Module. paste this code in:

    Option Explicit

    Sub ConvertToUpper()
    Dim i As Integer
    Dim j As Integer
    Dim rng As Range

    Set rng = Selection
    For i = 1 To rng.Rows.Count
    For j = 1 To rng.Columns.Count
    rng(i, j).Value = UCase$(rng(i, j).Value)
    Next
    Next

    End Sub

    You can now run it by selecting the cells you want converted, then Tools, Macros, Macros, selecting "ConvertToUpper" and Run.

    You can also assign it to a custom button. Tools, Customize, select the "Commands" tab (if it isn't already), scroll down on the left to select macros. There's a custom button there- drag that onto the toolbar. Then right click on the button, and select the "Assign to macro".

    I've probably left out a step or 2, but that's the gist of it.

    This will put the macro and button into the current worksheet. To make it available to all worksheets, you'll probably want to create the worksheet as an add-in. That's something I haven't done myself, so I'm not quite sure. I'll leave somebody else more knwledgeable to do that.

    Geoff
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Upper Case

    Geoff Whitfield suggested looping through the rows and columns in the selected range and substituting the UCase(text) for text in each cell.

    I think the approach is right, but runs into problems if the selected range does not start at A1, or is not a rectangular section of the s/sheet, I would think.

    I would suggest:
    ****
    Option Explicit

    Sub ConvertToUpper()
    Dim Rng As Range
    Dim Cell As Range

    Set Rng = Selection
    For Each Cell In Rng
    If Not IsNumeric(Cell.Value) Then Cell.Value = UCase$(Cell.Value)
    Next Cell

    End Sub
    ****

    The test for a numeric value is just to avoid overwriting formulas with their value equivalents. Unfortunately I couldn't find an equivalent "IsString" function, so I had to use the negation of the numeric test - there could be other values that you would want to avoid overwriting.

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Upper Case

    Just like the old carpenter's rule, "measure twice - cut once" I must remember to read twice, post once!

    First, there is no need for the "Rng" variable in my last bit of code - it will work perfectly well by using:

    For Each Cell in Selection...

    and probably a microsecond faster, too!

    Second, Geoff left open the question of how to make the macro available to all workbooks. Create it in your personal macro workbook, and if you are going to use it a lot, assign it to a (new) button on a toolbar. It will always be available to *you*, even if not to other users.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ** Upper Case Caution **

    Good catch, Tom!

    I think the code looks pretty good now!

  10. #10
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ** Upper Case Caution **

    I suggest the following revision to your code. If someone has created a logical formula that puts text in the cell for true or false, your code will overwrite their formula with the uppercase text result of the formula. (I would also add error handling if the macro was run on a selected chart or drawn object.)

    For example, if I said If(G2>40,"ot","") to say if the hours in cell G2 are greater than 40 then put ot in the cell, then the formula would have been overwritten with OT from your previous code, since IsNumeric would be false.

    The code below skips the cell if it contains a formula.
    <pre>Sub ConvertToUpper()
    On Error GoTo errConvertToUpper
    Dim Cell As Range
    For Each Cell In Selection
    If Not Cell.HasFormula Then Cell.Value = UCase(Cell.Value)
    Next Cell
    exitConvertToUpper:
    Exit Sub
    errConvertToUpper:
    If Err.Number = 438 Then
    MsgBox "You probably don't have cell(s) selected", vbExclamation, "Selection Alert"
    Resume exitConvertToUpper
    End If
    MsgBox Err.Number & " " & Err.Description
    Resume exitConvertToUpper
    End Sub
    </pre>


  11. #11
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upper Case

    Hi,

    My experience using "selection" has been in Word. In most cases there. It has been more efficient to set a range option- the selection object in most cases slows processing down for large selections drastically. The range object does not refresh the screen display like the selection object does.

    If there's a large selection, and this was REALLY important to code efficiently, I'd assign the output to an array, then at the end set the range back to the value of the array.

    When I was setting up a large range, and inserting values cell by cell, the response became extremely slow after a certain number of columns. I found that setting an array to the size of the selection, assigning the formula/value of each cell to the array, and then setting the range to the array, improved the response by 95%.

    Thanks for the reminder about "for each cell". I'd forgotten that I needed to define "cell" as a range.

    But then, it was a quick and dirty after all. Of course, I purposely left it up to other loungers to clean up the code!

    Geoff
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •