Can I capitalize an entire excel document at one time?

[Guest]

I am modifying a very large spreadsheet and need all the text cells
capitalized. Short of re-typing all the information, is there a way to
capitalize the entire document at one time?

 

[Paul B]

pfbear, here is one way, select your range and run this

Sub CAPS()
'select range and run this to change to all CAPS
Dim cel As Range
Application.ScreenUpdating = False
For Each cel In Intersect(Selection, _
ActiveSheet.UsedRange)
cel.Formula = UCase$(cel.Formula)
Next
Application.ScreenUpdating = True
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

Paul-

Thanks, but you are way beyond me. Is it possible to dumb it down a little
for an excel newbie?

 

[Bernie Deitrick]

pfbear,

Insert a new blank worksheet, and in cell A1, use a formula like

=UPPER(Sheet1!A1)

and then copy to enough cells to capture all of the data from sheet1.

If the furst sheet doesn't ahve any formulas, you can then select all the cells on your new sheet,
do a copy / pastespecial values, and delete the first sheet.

HTH,
Bernie
MS Excel MVP

 

[Paul B]

pfbear, if you want to give the macro a try,



To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is to the left of the "File" menu this will open
the VBA editor, in the left hand window click on your workbook name, go to
insert, module, and paste the code in the window that opens on the right
hand side, press Alt and Q to close this window and go back to your workbook
and press alt and F8, this will bring up a box to pick the Macro from, click
on the Macro name to run it. If you are using excel 2000 or newer you may
have to change the macro security settings to get the macro to run. To
change the security settings go to tools, macro, security, security level
and set it to medium



Sub CAPS()
'select range and run this to change to all CAPS
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Intersect(Selection, _
ActiveSheet.UsedRange)
cell.Formula = Ukase$(cell.Formula)
Next
Application.ScreenUpdating = True
End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

Bernie,

Thanks as well, but when I tried your formula, I got an Error Message saying
I had created a circular formula. I apologize for not understanding this,
but I am very new at excel and the formulas. Thanks again for your help.

 

[Guest]

Paul-

Thanks a ton!!!

pfbear, if you want to give the macro a try,



To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is to the left of the "File" menu this will open
the VBA editor, in the left hand window click on your workbook name, go to
insert, module, and paste the code in the window that opens on the right
hand side, press Alt and Q to close this window and go back to your workbook
and press alt and F8, this will bring up a box to pick the Macro from, click
on the Macro name to run it. If you are using excel 2000 or newer you may
have to change the macro security settings to get the macro to run. To
change the security settings go to tools, macro, security, security level
and set it to medium



Sub CAPS()
'select range and run this to change to all CAPS
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Intersect(Selection, _
ActiveSheet.UsedRange)
cell.Formula = Ukase$(cell.Formula)
Next
Application.ScreenUpdating = True
End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Bernie Deitrick]

pfbear,

On the new sheet that is entirely blank, enter this into Cell A1:

=UPPER(

Do NOT press Enter yet. Instead, click on the sheet tab of your other sheet, and select cell A1 of
THAT sheet. You'll see something like:

=UPPER('Other Sheet Name'!A1

Then press enter. Then copy and paste, etc.

HTH,
Bernie
MS Excel MVP