convert text in excel to uppercase

Is it possible to convert all text in a workbook or on a spreadsheet to all 
uppercase?
0
Elaine (442)
11/12/2004 4:37:02 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
469 Views

Similar Articles

[PageSpeed] 41

In VBA Editor

Sub MakeUpper()
    Dim MySht As Worksheet, MyCell As Range
    For Each MySht In ThisWorkbook.Sheets
        For Each MyCell In MySht.UsedRange.Cells
            MyCell = UCase(MyCell)
        Next
    Next
End Sub


"elaine" wrote:

> Is it possible to convert all text in a workbook or on a spreadsheet to all 
> uppercase?
0
Hayeso (14)
11/12/2004 4:55:05 PM
Hi
see:
http://www.cpearson.com/excel/case.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"elaine" <elaine@discussions.microsoft.com> schrieb im Newsbeitrag
news:BE5B2F08-104B-4EB7-8F29-DF1406C988BE@microsoft.com...
> Is it possible to convert all text in a workbook or on a spreadsheet
to all
> uppercase?

0
frank.kabel (11126)
11/12/2004 5:12:18 PM
There is no such example for this on Chip's page,  but you
probably want to change the
      MyCell = UCase(MyCell)
to
      MyCell.formula = UCase(MyCell.formula)
so you don't wipe out formulas.

My own solution would be  "Back to Kindergarten" in
     http://www.mvps.org/dmcritchie/excel/proper.htm#upper
which should be considerably, and ignores formulas and
empty cells.     Your   use of UsedRange   will eliminate the
vast ocean of empty cells, but not those within the used range.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Hayeso" <Hayeso@discussions.microsoft.com> wrote in message news:0CEF63C0-66E7-478A-B3EE-602E0DB3FB9C@microsoft.com...
> In VBA Editor
>
> Sub MakeUpper()
>     Dim MySht As Worksheet, MyCell As Range
>     For Each MySht In ThisWorkbook.Sheets
>         For Each MyCell In MySht.UsedRange.Cells
>             MyCell = UCase(MyCell)
>         Next
>     Next
> End Sub
>
>
> "elaine" wrote:
>
> > Is it possible to convert all text in a workbook or on a spreadsheet to all
> > uppercase?


0
dmcritchie (2586)
11/12/2004 5:57:29 PM
Elaine

A warning if you use this MakeUpper macro.

All formulas will be converted to values.

May not be a desired result.

An alternative......

Sub Upper_All_Sheets()
'David McRitchie
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim x As String, ws As Object, Cell As Range
    x = MsgBox("Use CANCEL to abort changing all constant " _
            & "cells to uppercase", vbOKCancel)
    If x = vbCancel Then Exit Sub
    For Each ws In ActiveWorkbook.Sheets
        On Error Resume Next        'In case no cells in selection
        ws.Activate
        For Each Cell In Cells.SpecialCells(xlConstants, xlTextValues)
            Cell.Value = UCase(Cell.Value)
        Next
    Next ws
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Gord Dibben Excel MVP

On Fri, 12 Nov 2004 08:55:05 -0800, Hayeso <Hayeso@discussions.microsoft.com>
wrote:

>In VBA Editor
>
>Sub MakeUpper()
>    Dim MySht As Worksheet, MyCell As Range
>    For Each MySht In ThisWorkbook.Sheets
>        For Each MyCell In MySht.UsedRange.Cells
>            MyCell = UCase(MyCell)
>        Next
>    Next
>End Sub
>
>
>"elaine" wrote:
>
>> Is it possible to convert all text in a workbook or on a spreadsheet to all 
>> uppercase?

0
Gord
11/12/2004 7:12:31 PM
Sorry about that David

In my posting I changed your "Kindergarten" sub to "Upper_All_Sheets"

Gord Dibben Excel MVP

On Fri, 12 Nov 2004 12:57:29 -0500, "David McRitchie" <dmcritchie@msn.com>
wrote:

>There is no such example for this on Chip's page,  but you
>probably want to change the
>      MyCell = UCase(MyCell)
>to
>      MyCell.formula = UCase(MyCell.formula)
>so you don't wipe out formulas.
>
>My own solution would be  "Back to Kindergarten" in
>     http://www.mvps.org/dmcritchie/excel/proper.htm#upper
>which should be considerably, and ignores formulas and
>empty cells.     Your   use of UsedRange   will eliminate the
>vast ocean of empty cells, but not those within the used range.
>---
>HTH,
>David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
>My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
>Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm
>
>"Hayeso" <Hayeso@discussions.microsoft.com> wrote in message news:0CEF63C0-66E7-478A-B3EE-602E0DB3FB9C@microsoft.com...
>> In VBA Editor
>>
>> Sub MakeUpper()
>>     Dim MySht As Worksheet, MyCell As Range
>>     For Each MySht In ThisWorkbook.Sheets
>>         For Each MyCell In MySht.UsedRange.Cells
>>             MyCell = UCase(MyCell)
>>         Next
>>     Next
>> End Sub
>>
>>
>> "elaine" wrote:
>>
>> > Is it possible to convert all text in a workbook or on a spreadsheet to all
>> > uppercase?
>

0
Gord
11/12/2004 10:01:43 PM
Reply:

Similar Artilces:

Dependent Formatting (Text or Numbers
Hello All, I am attempting create formatting for an object based upon the value displayed. If the value is six numbers entered without spaces (Ex. 123456), then the formatting should be "##-##-##" (Ex. 12-34-56). Otherwise, the object must allow regular text. I have tried several different methods but with no luck. Below is my most recent attempt. I tried inserting it into the "lost focus", "on change", and "after update actions". Many thanks in advance for helping clear up a headache. If Me.AccountCode1 Like "######" Then Me.AccountCode...

Excel file name
Recently, Excel started saving my spreadsheets with the name I give the file followed by the .xls extension. Now, when I use the file name in the header or footer it appears on my printed report as "ABC Report.xls" rather than just "ABC Report". I don't want the .xls to appear, and can't figure out how to get rid of it. What happened and how do I change it back? I am using Excel 2002. Thanks for your help on this small, but very annoying problem. You probably changed an option in Windows Explorer. I'd leave it at the new setting (it's safer to see th...

Exporting EXCEL data in text (cvs) format
Is there no way to export EXCEL data to a text format e.g. cvs : "StringField1","StringField2",NumericField3,NumericField4,"StringField5" etc.? You can import from this format, why no export? Any work around? I'm the first person in the universe who wants an importable text version of his EXCEL data? Thanks! Woops! I didn't see the other options in the save as pulldown list. Sorry! Don't bother! "Simon Wigzell" <simonwigzell@shaw.ca> wrote in message news:2FCNb.115599$X%5.16110@pd7tw2no... > Is there no way to export EXCEL da...

Avoid automatic text to column split while pasting data
I am copying data from Access and pasting it in Excel. The column containing "LastName, FirstName" is automatically getting pasted in 2 columns. I want it to get pasted in one column only. Currently ColA ColB John| Doe I need it to be ColA Thank you! John, Doe You probably used Data>Text to Columns>Delimited by "comma" for some other operation. Excel remembers this.........thank you much<g> Run through Data>Text to Columns with some dummy text and change the delimiter to nothing. Gord Dibben MS Excel MVP On Wed, 25 Jun 2008 09:41:00 -0700, Query...

Compare text string of a cell in Column A VS another cell in Colum
Hi All, I would appreciate greatly if any vba expert could help me with this solutioning urgently as my deadline is drawing near (today) and i could not resolve this solution for cell comparison. I have a vba function code below written in such a way that it compares a cell in Column A versus another cell in Column B. But both cells must be the same row in order to compare. Say cell A2 contains "M O E" and cell B2 contains "Ministry of Education" and my function in cell C2 which is = Fuzzy(A2,B2), will give me 100% correct percentages. The other example is where ...

Passing a cell value into a text box on a graph
Please help. I am trying to pass a value from a cell onto a graph. The graph is plotting culumative variance against a plan but the gap in the early days is very small. So I need to publish the gap between plan and actual on the graph. I want to automate the update of the gap on opening the spreadsheet or pressing a button. The calculation of the gap is no problem for me, its the putting the answer onto the graph is the issue. I thought I could do it via a text box on the graph. Thanks in advance John Please help. I am trying to pass a value from a cell onto a graph. The graph is plot...

Converting database from Access 97 to 2003: problems
Hi, I am trying to convert an Access 97 database to Access 2003. I am using a workgroup file to secure the database. I have split the database in a front end and a backend. The backend only contains tables. Both databases give problems when trying to convert to an Access 2003. I am describing the conversion of the frontend database. The problem is that i cannot open the tables. This message appears: Reserved error (-1104); there is no message for this error. Explanation of the steps i did. First thing that i did is: started Access 2003 and selected the correct Access 97 workgroup file...

Change chart type in single data series, Excel 2007
I can't get it to change the chart type on a single data series in the new Excel 2007. The "change chart type" is grayed out. Thank you, What kind of chart is it? If it's a 3D chart, you can't change only one series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "momof3" <momof3@discussions.microsoft.com> wrote in message news:F78063A1-DB29-4081-B909-BDA99F2DEBFD@microsoft.com... >I can't get it to change the chart type on a single data series ...

formatted colored text
When I format text and/or numbers to be in color, it continues to show as black and white on the screen. What am I doing wrong? Check this out................ http://support.microsoft.com/default.aspx?kbid=833258 The fill color, the fill pattern, or the line color of a WordArt or AutoShape object in an Office document does not change As directed, change the High Contrast setting in Windows OS for users' profiles. Gord Dibben MS Excel MVP On Fri, 28 May 2010 14:08:09 -0700, Brenda <Brenda@discussions.microsoft.com> wrote: >When I format text and/or ...

Unable to convert money 2001 data to 2005
Hi Ho, How to Convert 2001 data to 2005 Thanking Yoiu/ Presuming both the versions of 01 and 05 were produced in the same country, it should just work. What error do you get? -- "George J Meier" <jost@meier.screaming.net> wrote in message news:%23qoGmWOmFHA.3568@tk2msftngp13.phx.gbl... > Hi Ho, > > How to Convert 2001 data to 2005 > > Thanking Yoiu/ > Thanking you Richard, I am doing this for the person who had the trouble. He said the programme asked him which country he was in, and he said England. The programme then asked him if he wanted...

Solver Add-In for Excel
Where can I download the Solver Add-In? "cram" <cram@discussions.microsoft.com> wrote in message news:3A0A8CDC-F6D4-48C5-AB01-6BA30D5B8B0C@microsoft.com... > Where can I download the Solver Add-In? With Excel 2003 it's on the cd: Tools - Add-Ins then select the Solver Add-In box. Hi you can't download it. It should be on your installation CDs -- Regards Frank Kabel Frankfurt, Germany cram wrote: > Where can I download the Solver Add-In? With all versions of Excel it is on the CD. You can purchase an enhanced version at http://www.solver.com/ J...

working out which colour text is used to populate a cell
Hi all, Fingers crossed someone out there will be able to help me. I have been tasked to provide a spreadsheet at work to monitor a number of applications and written applications that we receive and the number of days we respond by. I have the following cells ‘application received’(Cell B), ‘enquiry received’(Cell C), ‘date completed’(Cell E) I then have the following formula in Cell H =NETWORKDAYS(IF(B4<>"",B4,C4),E4,$A$119:$A$158) which determines which cell has the date in (B or C) and then works out the number of days it has taken looking at cell E (the...

OL2000: BUG: Outlook composes message in Rich text format when the "mailto" link contains the "body" parameter
Example: mailto:xxx@xxx.xxx?subject=hi - opens a new message in plain text or HTML text, based on the default setting mailto:xxx@xxx.xxx?subject=hi&body=test - opens a new message in Rich text. User then has to manually change to plain text. How can I submit this bug to Microsoft? Thanks, Ivan Ivan B�tora <xxx@xxx.xxx> wrote: > Example: > > mailto:xxx@xxx.xxx?subject=hi - opens a new message in plain text or > HTML text, based on the default setting > > mailto:xxx@xxx.xxx?subject=hi&body=test - opens a new message in Rich > text. User then has to manu...

Wordwrap failure in long text
Excel 2000 SP-3, Win 98. I often have long text cells, with several paragraphs. At some point, they all get the same sickness, wordwrap doesn't work anymore. The lines that wrapped up to that point still do. After the mystery point, the only way to get a line break is Alt-Enter. Mid-December 03, there was a short thread on this in Crashesgpfs (look up "Maximum row size"). The one answer was that there is a max row size of 409, so after 409 chars no more wordwrap. Sounds like my problem. The workaround would be to quit the current cell at the previous paragraph break,...

Convert text to number
Hi people, Thanks a lot to everybody. Unfortunatelly I cannot attach .xls file that I receive, because of excelforum's restrictions, it looks like usual excel file. I will try to use the advice of Debra. Thanks again, Irina. -- Irina ------------------------------------------------------------------------ Irina's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8685 View this thread: http://www.excelforum.com/showthread.php?threadid=263454 ...

Excel 2007
I cannot find the option to fill my charts with patterns (lines, dots, crosshairs, etc.) in MS 2007. Help, please. Thanks, Excel 2007 is a Windows version � you've posted to the group for Excel on the Mac. However, when you select a chart in 2007 there is a special set of contextual tabs that appear towards the right end of the Ribbon. Explore them as well as right-clicking chart components & selecting Format [Whatever] from the shortcut menu. But if you're looking for the same mundane bitmap Patterns provided in previous versions you won't find them. They may ...

Excel protecting formatting
Hi All How can I prevent cell formats from being removed or being overwritten when data is cut or pasted from another cell. I do not wish to rely on users having to use the "Paste Special" option. Many thanks Maybe you could just intercept the change. This worked in light testing. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myFormulas As Variant On Error GoTo errHandler: myFormulas = Target.Formula With Application .EnableEvents = False .Undo End With Target.Formula = myFormulas errHandler: ...

Pocket Excel
Microsoft REALLY needs to support Excel drop down boxes for the pocket PC. Inputting is cumbersome with the Pocket PC and if there ever was an application that where drop downs would be useful it's on pocket excel. I realize some functionality must be sacrificed .... but not drop downs! How about a patch to upgrade? Hi as this is a peer-to-peer newsgroup you may try emailing MS directly: mswish@microsoft.com -- Regards Frank Kabel Frankfurt, Germany "brian" <brian@discussions.microsoft.com> schrieb im Newsbeitrag news:4C8E8833-6EC6-45CC-82FC-6830F2B25FCF@microsoft....

Convert or use .mas file
I have received a .mas file, presumably an access stored procedure shortcut, and it opens with access on double click, but I cannot seem to get at any of the data in it. Inspection with notepad shows that there is table-type data in it, which I'd like to extract. Does anybody here know how to do this? Tx -- John ...

Can I convert my .mdb file to a binary file for Subversion?
Hi I am trying to use TortoiseSVN Source Control on my Microsoft Access databases, but it only works on binary files. Does anyone know how I can m\ke my .mdb files into binary? I tried looking at the Microsoft products, Visual Source Safe etc, but to be honest, their website is a maze and I cannot find a version to download anywhere. Stapes ms-access does work with Microsoft's Visual Source safe control system. As for converting to binary? why? Do you convert your c++ code into binary to make it source safe compatible?? (this is just nonsense). If TortoiseSVN supports the standard i...

Using excel if function on a pricing spreadsheet
I am trying to simplify a pricing spreadsheet that I use at work. I a using the following criteria; the $ value of a product, the descriptio of a product that I have on a cost page. What I want to do is when type in the description of product X in a specific cell, I want the value of that product returned to another specific cell. Example: Lexan14SS (in a cell); 1.75 ($ value in another cell). On a pricing spreadsheet I type in Lexan14SS and I want that specific value returned to another cell. Of course though i will have mutipl product descriptions and prices to go along with those produc...

Need a formula for Excel 2000..
Greetings & bless your little cottons for existing. I've never don anything more than autosum before and now my workplace has gifted m with a spreadsheet project that looks monumental to me. Can anyone tel me how..... If, in Sheet 1, the cell range C2-C41 contains "Cohen" anywhere AND th cell range R2-R41 contains "allowed" anywhere, I need to have th number of times that these two conditions are satisfied displayed a that number of times, in Sheet 2 at cell B2. So far I have =(IF((C2:C41="Cohen")*(R2:R41="Allowed"),) I can't find the command...

Excel template similar to MS Money less investments?
Is there an Excel template or freeware that does the recording functions of M/S Money but doesn't involve investments and live updating ? Basically I'm looking for a check book program that makes some reports possible. ...

cant open Excel 2000 workbook with macros in XP
H I cant open any excel spreadsheats with macros that were made previously the come up saying this has a macro from 4 cant open in high security environmen Can anyone help????????????? Try changing the security settings on the xp excel. -- Don Guillett SalesAid Software donaldb@281.com "Murray Thompson" <murree@value.net.nz> wrote in message news:BE2D0018-BF40-4EF8-9394-390534E1AFB5@microsoft.com... > Hi > I cant open any excel spreadsheats with macros that were made previously the come up saying this has a macro from 4 cant open in high security environment > Can a...

Importing into Outlook 2003 from Excel (ss created in Office 2000)
Am having difficulty Importing data into Outlook/ Contacts 2003 from an older spreadsheet created in Office 2000. Everything in steps to import works except the actual transfer of data: e.g., the first field in the Excel spreadsheet ("City" field) imports correctly into Contacts (without any of the other imported fields). Then blank records are added for as many records there are in the next field: "Company" name. Have cleared all Flags during the Import/Export function in Outlook and am totally stumped as to how to fix. Thanks so much for the help. Rebel ...