CHANGE WHOLE EXCEL worksheet TO UPPERCASE LETTERS?

My boss wants me to change a whole excel document to uppercase letters. It 
has like 43,000 records. How do I do that? He says it's possible, but I've 
never seen it done. I'm Using Excell 2003.
0
9/1/2005 4:51:04 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
408 Views

Similar Articles

[PageSpeed] 28

Your boss must be thinking of using a macro. Take a look here:

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



In article <608169EE-E690-4B26-AFD2-2FE9ECEBE856@microsoft.com>,
 "mineralgirl" <mineralgirl@discussions.microsoft.com> wrote:

> My boss wants me to change a whole excel document to uppercase letters. It 
> has like 43,000 records. How do I do that? He says it's possible, but I've 
> never seen it done. I'm Using Excell 2003.
0
jemcgimpsey (6723)
9/1/2005 5:10:43 PM
JE's right about having to use a macro.  I would just point out that this 
will take a long time to run because of the amount of data in the sheet. 
You might want to run it overnight.  Also the macro requires you to select 
all the cells to convert first.  If you want to convert all the text entries 
without selecting use this:

Sub Upper_Case()
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim Cell As Range
   On Error Resume Next
   For Each Cell In Cells.SpecialCells(xlConstants, xlTextValues)
      Cell.Formula = UCase(Cell.Formula)
   Next
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

-- 
Jim
"mineralgirl" <mineralgirl@discussions.microsoft.com> wrote in message 
news:608169EE-E690-4B26-AFD2-2FE9ECEBE856@microsoft.com...
| My boss wants me to change a whole excel document to uppercase letters. It
| has like 43,000 records. How do I do that? He says it's possible, but I've
| never seen it done. I'm Using Excell 2003. 


0
jrrech (1932)
9/1/2005 5:22:46 PM
How many worksheets is the data in?

It's possible to just create a new worksheet and use
=UPPER(Sheet1!A1)  and copy it down and across to match the rows and column
count of the original worksheet.

Steve

"Jim Rech" <jrrech@hotmail.com> wrote in message
news:ejnBBnxrFHA.3080@TK2MSFTNGP15.phx.gbl...
> JE's right about having to use a macro.  I would just point out that this
> will take a long time to run because of the amount of data in the sheet.
> You might want to run it overnight.  Also the macro requires you to select
> all the cells to convert first.  If you want to convert all the text
entries
> without selecting use this:
>
> Sub Upper_Case()
>    Application.ScreenUpdating = False
>    Application.Calculation = xlCalculationManual
>    Dim Cell As Range
>    On Error Resume Next
>    For Each Cell In Cells.SpecialCells(xlConstants, xlTextValues)
>       Cell.Formula = UCase(Cell.Formula)
>    Next
>    Application.Calculation = xlCalculationAutomatic
>    Application.ScreenUpdating = True
> End Sub
>
> -- 
> Jim
> "mineralgirl" <mineralgirl@discussions.microsoft.com> wrote in message
> news:608169EE-E690-4B26-AFD2-2FE9ECEBE856@microsoft.com...
> | My boss wants me to change a whole excel document to uppercase letters.
It
> | has like 43,000 records. How do I do that? He says it's possible, but
I've
> | never seen it done. I'm Using Excell 2003.
>
>


0
9/2/2005 1:40:43 PM
Hi Steve and mineralgirl,
She did not say, but if you look in the subject title it indicates one
because it says a whole Excel worksheet.

So I didn't add to Jim's reply.  But since you have kind of brought
it up,   if one did wants to convert the
entire workbook there is also one of those on my page, and you
can perhaps tell from the topic title what I think of converting to
and looking at all capitals whether it is an entire workbook, or
and entire worksheet.
    http://www.mvps.org/dmcritchie/excel/proper.htm#kindy

For one worksheet, I can't see having a separate macro, because
it is so easy to select all cells and run the same macro as you would
use to simply change a column or other selection.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htmSearch Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Steve McBride" <stevemcb@nospam.hellsouth.net> wrote in message news:Ow8xyN8rFHA.3640@tk2msftngp13.phx.gbl...
> How many worksheets is the data in?
>
> It's possible to just create a new worksheet and use
> =UPPER(Sheet1!A1)  and copy it down and across to match the rows and column
> count of the original worksheet.
>
> Steve
>
> "Jim Rech" <jrrech@hotmail.com> wrote in message
> news:ejnBBnxrFHA.3080@TK2MSFTNGP15.phx.gbl...
> > JE's right about having to use a macro.  I would just point out that this
> > will take a long time to run because of the amount of data in the sheet.
> > You might want to run it overnight.  Also the macro requires you to select
> > all the cells to convert first.  If you want to convert all the text
> entries
> > without selecting use this:
> >
> > Sub Upper_Case()
> >    Application.ScreenUpdating = False
> >    Application.Calculation = xlCalculationManual
> >    Dim Cell As Range
> >    On Error Resume Next
> >    For Each Cell In Cells.SpecialCells(xlConstants, xlTextValues)
> >       Cell.Formula = UCase(Cell.Formula)
> >    Next
> >    Application.Calculation = xlCalculationAutomatic
> >    Application.ScreenUpdating = True
> > End Sub
> >
> > -- 
> > Jim
> > "mineralgirl" <mineralgirl@discussions.microsoft.com> wrote in message
> > news:608169EE-E690-4B26-AFD2-2FE9ECEBE856@microsoft.com...
> > | My boss wants me to change a whole excel document to uppercase letters.
> It
> > | has like 43,000 records. How do I do that? He says it's possible, but
> I've
> > | never seen it done. I'm Using Excell 2003.
> >
> >
>
>


0
9/3/2005 12:29:07 AM
Reply:

Similar Artilces:

Excel on website
Hi, I have an Excel file which opens maximum at my desk, but it opens minimum when I view it on my company's intranet. I use FrontPage 03 and Excel 07. Please advice! Thanks Chi After moving the file to server and linked it to the page I got it! Thanks Chi "Chi" wrote: > Hi, > > I have an Excel file which opens maximum at my desk, but it opens minimum > when I view it on my company's intranet. I use FrontPage 03 and Excel 07. > > Please advice! > > Thanks > Chi > > > > > > Aft...

Sum
Hi, I just found this "feature" at Excel formulas today. Please, have a look at: http://danielgudang.multiply.com/journal/item/192 (in portuguese, but images show all) Let me explain: some cell C1 = sum (C2:C5) some cell D1 = sum (D2:D5) some cell E1 = sum (E2:E5) A1 = C1 - D1 - E1 B1 = C1 - (D1 + E1) A1 = B1 ??? Oh, not always! Sometimes A1 will be +0, sometimes -0. Really strange! It's a feature of any application that uses IEEE double precision floating point math (e.g., every commercial spreadsheet I know of). It's the result of having finite precision ...

Worksheet Changes
Hi All, HYCH Have a worksheet that has data in the range A5:I105, would like cell a2 to show a date that any of this data has changed, was thinking of using a worksheet change_Event to manage this but not sure of how to go about setting this up, have used the simple change event for a single selection i.e A1 or B4 but not with a range area. Any help would be Great Steve A bit more explanation along with layout and what desired output looks like. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Steve" <instructorf@hotmail.com> wrote in mes...

Drop-down Lists and Worksheet Protection
In Excell 2000 I could set "Validate/Drop down List" for a protected cell and when the worksheet was protected the cell could be "edited" using the drop-down list but not otherwise. Moreover, contents of the cell could be changed but not cleared. Try as I may I can't sem to achieve the same in Excell 2003. Any one have a solution? In Excel 2000, you can select from a data validation list if the source was a worksheet range, but not if the source was a delimited list. In Excel 2003, neither is allowed on a protected sheet. Perhaps you could use a combo box, instea...

Worksheet disappear when saving on shared workbook
Im Using Excel 2000 SP3, when i try to copy a cells from another workbook and paste it on the shared workbook, the worksheet of the shared workbook disappeared after saving it. Please help. ...

RE: MSCRMDeletionService -- change frequency...
i am trying to modify the frequency for the MSCRMDeletionService...there is a registry value in the HKLM\Software\Microsoft\MSCRM\ key named DSRunInterval. one of our servers has this value as a REG_SZ (string) value while another server has it as a DWORD (number) value ; does anyone have an idea what format this value should be? and also is it based on seconds? thanks! sunish ...

Customise doesn't keep changes.
Hi!! I have Money 2005... I would like to customise some report in order to avoid some categories in reports. I did it for "Spending by Categories" report.. I ommited some and it worked great, howevver, when I return to home the report still show the categories I ommited and when I go to the report waht I customised is gone. Is there a bug in this? Can I create new report from nothing at all? or from a template? In microsoft.public.money, Marcell wrote: >Hi!! >I have Money 2005... I would like to customise some report in order to avoid >some categories in reports....

changing the delimiter while saving as txt file
Dear Experts, is there a way to change the delimiter to # instead of tab characters while saving the excel file to .txt file. Thanks in advance Hi see: http://www.cpearson.com/excel/imptext.htm -- Regards Frank Kabel Frankfurt, Germany "raj" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:845d01c48520$92fc1c20$a401280a@phx.gbl... > Dear Experts, > > is there a way to change the delimiter to # instead of tab > characters while saving the excel file to .txt file. > > Thanks in advance And some more links... Maybe you can steal some cod...

Embedded Excel worksheet in PowerPoint
I'm working with an embedded excel worksheet in powerponit. The doc is sent to me and if I have to edit the worksheet, I find that some cells have missing characters once I open the worksheet to edit it. It appears that characters beyond some length (256?) are being truncated off. How do I resolve this so I don't have this problem again? Thanks, Barb Reinhardt This kind of problem may occur if the material was inserted into the embedded worksheet via copy/paste from an external sheet. A reliable approach is to open the embedded sheet first, and from that sheet do: File ...

OWA Change password option
I've read through your various postings and articles, relating to OWA change password option under a front-end / back-end 2003 SP2 senario. Please can you clarify the following steps I need to follow: 1) Create a new Iisadmpwd virtual directory in IIS on the frontend server. 2) Run the OWAADMIN tool, and select enable password feature on both the front-end and back-end servers. Can you clarify if by using the OWAADMIN tool, that I won't have to manually edit the registry on both servers as the tool will take care of this for me? Can you also clarify, which services (if a...

Excel 2003 01-27-10
Hi all, hopefully you can help me. In cell B21 the user will always input an email address, i have created a button and now need the button to automatically email the email address in cell B21. I then need it to default and save to a specific location with the file name automatically generated from b12. Please help. :) ...

Can't change default print resulotion in Publisher 2003
In Publisher 2003, I tried to change the print quality via the "Printer Properties" dialog box, but that didn't change the print resolution. I clicked "Advanced Print Settings..." in the print dialog box, but the only drop-down choice next to "Resolution" is "Default," which doesn't say what the default is but seems to be set at Super-fine. How do I cange the print resolution to Normal or Draft? Thank you. Don Burgess <DonBurgess@discussions.microsoft.com> was very recently heard to utter: > In Publisher 2003, I tried to change...

Changing Functional Currency #3
Hi There is a live company ABC. We created the new company XYZ then I restore the live company database ABC to the newly created company XYZ by following the KB article 871973. Then we delete the all the transaction in XYZ company because we need the same company setup and masters with that we added required additional master data. The problem we are facing is we need to change the functional currency in XYZ company. How can we do this? Any one can help us Regards Nizham I don't believe you can change the functional in XYZ since there were transactions. Howabout creating a 123 com...

Purge data based on another Excel file???
I am attempting to purge data from a large list based on data in another document. I have the files I want to remaove in on file and the file I want to remove them from. There should be simple way to do this no? ...

Changed relationship; records no longer linked
I have a Corporates table holding details of company customers. I have a CorpContacts table holding details of our contact persons at those companies. Originally I had a relationship (one to many) between Corporates.CorpName and CorpContacts.CorpName. I had a Corporates form which contained a CorpContacts subform displaying the contact persons' details. All working fine. But if we updated the name of the company i.e. Corporates.CorpName, the link was lost and the contact persons were no longer displayed. So I introduced new fields in each table ("CorpID"), i.e. some...

Changing OLAP Pivot Table Connection Source
I have numerous spreadsheets that have pivot tables pointing to an OLAP Cube via a connection string. Is there an easy way (either automated or manual) to change the connection string to point to a new version of the OLAP Cube? I can edit the connection properties and navigate to the new .odc connection file, but get an error saying "The OLAP provider returned an unexpected number of dimensions." ...

in Excel: How do I change "selected cells" highlight color?
When I select cells in Excel the shading is so light (blue) that I can barely tell which cells are selected from those that are not. Can I change the background color of cells that are selected? Thanks. Assuming Excel 2007 this is a known problem and as yet no patch for it. I don't have Excel 2007 but I have read that playing with your Contrast can help some. Gord Dibben MS Excel MVP On Tue, 10 Jun 2008 12:51:00 -0700, flameretired <flameretired@discussions.microsoft.com> wrote: >When I select cells in Excel the shading is so light (blue) that I can barely >tell whic...

EXCEL 2007
I have a GIS application which uses .dbf files (not sure if they are actually Dbase files). When I need to append data (fields) to the files, I have a big problem in that excel 2007 no longer allows save as / export to .dbf files. If I try bring the data in MS Access and save as .dbf files, I have untold problems with the GIS application... The only way I have been successful with this is to find a user with an older version of excel, insert the data as database columns and then save as .dbf Any suggestions as to how I can get around this with out purchasing a converter? ...

How to make A1 the active cell in all visible worksheets ?
This is a multi-part message in MIME format. ------=_NextPart_000_0028_01CA8164.76257740 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ......so that when i activate the sheet A1 is the cell in the upper left = corner of my screen. Thanx ------=_NextPart_000_0028_01CA8164.76257740 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3D"text/html; ...

email from excel workbook button does nothing
In my old version of excel(2003) as long as i had outlook open i could email directly from the worksheet. Now i have office 2007 with "windows mail" when i press the button in the "quick access" tool bar it does nothing. I have tried having widows mail open when I do it too but the only way i can send is by making file attachments. seems like the long way around. Is there something I am doing wrong? I have tried to read some of the previous posts and dont understand anything about codes ect. Please help, Mark hi Mark Is Windows Mail your default m...

Change Percentage DIV/0! Error
I'm trying to see the change between (A) 2002 unit sales and (B)200 unit sales, and am using the following formula: =(B-A)/A This gives me the difference negative or positive, however if A (200 sales) is zero, then I get the DIV/0! error, how do I fix this? Thank you -- Message posted from http://www.ExcelForum.com Hi, A couple of things you might want to consider. One is that your formula will not take into account what the change is if the prior month base amount is negative. To fix that use the =IF(base=0,"",(current-base)/ABS(base)) This should help >-----Or...

Delete duplicate rows from a list in Excel
Hi, Below are instructions on how to "delete duplicate rows from a list in Excel". I learned about this tip from the Microsoft Office Assistant website. However, each time I try step 4, my list is not filtered and no records are hidden. Is there a secret I am missing to make this work? Thanks for your help! -Greg http://office.microsoft.com/en-us/assistance/HA010346261033.aspx Applies to Microsoft Office Excel 2003 Microsoft Excel 2002 A duplicate row (also called a record) in a list is one where all values in the row are an exact match of all the values in another row. To delet...

How to tell Excel to insert cells and shift down from Access
Hi all, I have Access 2003. I am trying to tell Excel to insert cells and shift cells down from Access, with this line of code: oWksh.Cells.Insert Shift:=xlDown But Access does not like that line. It gave me run-time error 1004. I know if has to do with the part Shift:=xlDown Can you suggestion what I can do? Thank you in advance, Ben "Ben" <Ben@NoSpam.com> wrote in message news:eSZuzcDpKHA.1892@TK2MSFTNGP02.phx.gbl... > Hi all, > > I have Access 2003. I am trying to tell Excel to insert cells and shift > cells down from Access,...

data links on subsequent worksheets
I have main data entered on worksheet 1 that I have linked to subsequent worksheets, however when there is no data on worksheet 1 I get 0's printed in the data space. How doI eliminate the 0? If you want to hide all zero values on the sheet, Tools --> Options --> [View] tab and clear the checkbox next to "Zero Values" In Excel 2007, that's "Office Button" --> [Excel Options] --> then [Advanced] in left pane and scroll down to the "Display Options for this Worksheet" and clear the box next to "Show a zero in cells that have a...

Text Wrap in Excel 2002
I have been having difficultly wrapping text. I merge several cells and wrap text sucessfully. However, if I go into one of the cells and add a couple of sentences and enter, my text that was wrapped perfectly is simply a string of #########'s and I can't view the text correctly by changing the row height or reformatting (even though it is all still there - as I can see in the edit box - when my cursor is on the text box). Any suggestions? Try formatting the cell as General. bdean wrote: > > I have been having difficultly wrapping text. I merge > several cells an...