Thanks again for the help, Dave and Gord.... I did enjoy the slight hints of
sarcasm (even if it was not intended.) ;)

Though the macro does exactly what was needed and more (e.g. capitallizes
and even removes the formula). I think presenting that to the average user
might be a bit extreme. Unless they don't mind having to import the macro
every time they start a new workbook that might require this function.... I
think it's easier for them to learn the referenced/drag & fill, and just
hide/lock the referenced column if they don't want to see it.

Thanks again to the both you.

---------

"Dave F" wrote:

Ha.
--
Brevity is the soul of wit.


"Gord Dibben" wrote:

Using Worksheet Function UPPER there is no easier way than =UPPER(A1) and
drag/copy or fill series.

The easiest way is to run a macro on a selected range to change to UPPER all at
once with no formulas involved.

Sub optUpper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = UCase(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Your boss should know how to install and implement this code since she/he
insists there is an easier way.


Gord Dibben MS Excel MVP


On Tue, 5 Sep 2006 10:31:03 -0700, Shohoku79
wrote:

Ok, I understand that in Excel, to convert a string of texts to all caps
could be done by using the UPPER formula ( e.g. UPPER("Text") )
My supervisor came to me and asked me, she has a column full of street
addresses in mixed cases that she needs to convert to all caps.

Example:
123 Main Street
1412 south avenue
592 northern main drive
243 EAST ROAD

I tried the UPPER formula on the first one and wanted to do the same for all
the others, but the Fill series / paste special all seemed to end up copying
the contents of the first cell rather than repeating the =UPPER("text") for
the series. Then I thought about doing this by reference. So I copie
generated a seperate column and used the reference formula (=UPPER(A1)) and
filled series, this worked. But my boss complained and said this is too
complicated and insisted that there is an easier way.

My question now is, is there any way to do this without having to use
reference?

Thanks a bunch.