changein text to all caps

[Deanna]

is it possible the text in an excel document ot all caps?

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[JE McGimpsey]

See David McRitchie's UPPER() macro:

http://www.mvps.org/dmcritchie/excel/proper.htm#upper

and if you're unfamiliar with macros, see his "Getting Started with
Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Ken Wright]

Various subs *borrowed* from others in here.

Sub CAPS1()
'select range and run this to change to all CAPS
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim Cel As Range
For Each Cel In Intersect(Selection, ActiveSheet.UsedRange)
Cel.Formula = UCase$(Cel.Formula)
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub CAPS2()
'Just run this and it will affect the whole sheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = False Then
cell.Value = UCase(cell.Value)
End If
Next cell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub CAPS3()
'select range and run this to change to all CAPS
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim myCell As Range
Dim myRng As Range

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range that contains text--no formulas!"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = StrConv(myCell.Value, vbUpperCase)
Next myCell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Bill Ridgeway]

Yes - although it's a bit circuitous.

The general idea is to copy and paste into a Word file. Use the
<Format.<Change case> facility then copy the whole lot back into excel.

It works!

Regards.

Bill Ridgeway
Computer Solutions

 

[Ken Wright]

It works!

But makes no sense whatsoever to do so when the same can be achieved quite
easily without ever leaving the application.

 

[JE McGimpsey]

Have to disagree.

Much as I like macros, for a one-off it's a heckuva lot faster to pull
some columns into Word, change case and put it back.

Especially if you're not familiar with macros.

Naturally, the macro route seems right to me when it's a recurring issue.

 

[JoJo]

Very easy to do in Excel by using the =UPPER function.

For example in cell c3 you could have "abcde" which you then want to make
into upper case. Insert a new row or column next to what you want to
change, type in
=upper(c3) and enter - there you go. Once you do this for all relevant
cells highlight your new column (or row) and do copy>paste special>as
values - your formula is gone and you are left with caps. You can now
delete your original info.