Upper / Fill Series Function in Excel

[Guest]

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.

 

[Guest]

=UPPER(A1)

Select that, hold the shift and down arrow and release these keys when you
get to the end of your range. Then CTRL+D.

The method you were trying is for data series, i.e., Monday, Tuesday,
Wednesday, etc.

Dave

 

[Guest]

Another thing to consider is that if your boss doesn't want to see the
formulas, just copy the column, select paste special--values and click OK.

Dave

 

[Guest]

Thanks Dave for your response.

Well, the method you provided is what I already tried. That is, if the
addresses were in column A, and in Column B, I reference the column using
=UPPER(A1) and drag down to fill series, then it will work.

What my boss wanted was finding out if there was a way to:

First Do
=UPPER("123 Main Street")

and then using the fill function so that the formula format =UPPER("text")
will be applied to subsequent cells automatically.
=UPPER("1412 south avenue")
=UPPER("592 northern main drive")
=UPPER("243 EAST ROAD")

I thought I could get by with using the reference column, but she wants a
simple way where the average users will know how to do this. Simplest way
would've been a built-in excel function that will change all highlighted cell
contents to either UPPER/LOWER/PROPER case. But I don't think there is that
function, or is there...?

 

[Gord Dibben]

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

 

[Guest]

Ha.
--
Brevity is the soul of wit.

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

 

[Guest]

You'd have to create named ranges for each of those text strings, which is
not what the average user knows.

Does your boss understand Excel? The way you've done it is the simplest way
to do it.

Dave

 

[Guest]

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.

---------