Upper Case

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

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

Reply | Quote

Good catch, Tom!

I think the code looks pretty good now!

Reply | Quote