Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can I capitalize an entire excel document at one time?
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? |
#2
|
|||
|
|||
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 "pfbear" wrote in message ... 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? |
#3
|
|||
|
|||
Paul-
Thanks, but you are way beyond me. Is it possible to dumb it down a little for an excel newbie? "Paul B" wrote: 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 "pfbear" wrote in message ... 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? |
#4
|
|||
|
|||
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 "pfbear" wrote in message ... Paul- Thanks, but you are way beyond me. Is it possible to dumb it down a little for an excel newbie? "Paul B" wrote: 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 "pfbear" wrote in message ... 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? |
#5
|
|||
|
|||
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 "pfbear" wrote in message ... Paul- Thanks, but you are way beyond me. Is it possible to dumb it down a little for an excel newbie? "Paul B" wrote: 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 "pfbear" wrote in message ... 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? |
#6
|
|||
|
|||
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. "Bernie Deitrick" wrote: 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 "pfbear" wrote in message ... Paul- Thanks, but you are way beyond me. Is it possible to dumb it down a little for an excel newbie? "Paul B" wrote: 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 "pfbear" wrote in message ... 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? |
#7
|
|||
|
|||
Paul-
Thanks a ton!!! "Paul B" wrote: 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 "pfbear" wrote in message ... Paul- Thanks, but you are way beyond me. Is it possible to dumb it down a little for an excel newbie? "Paul B" wrote: 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 "pfbear" wrote in message ... 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? |
#8
|
|||
|
|||
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 "pfbear" wrote in message ... 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. "Bernie Deitrick" wrote: 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 "pfbear" wrote in message ... Paul- Thanks, but you are way beyond me. Is it possible to dumb it down a little for an excel newbie? "Paul B" wrote: 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 "pfbear" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel document protection | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
my excel document won't open unless i go file, then open | Excel Discussion (Misc queries) | |||
When I open excel document it always ask if I want to open it in . | Excel Discussion (Misc queries) | |||
Is there any way to convert a pdf document into an excel document. | Excel Discussion (Misc queries) |