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
437 Views

Similar Articles

[PageSpeed] 20

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:

Auto convert PST/POP to Exchange 2003 mailboxes?
We are about to convert our users from PST files (POP3) to Exchange 2003 mailboxes. As there are several hundred of users, this would be way too heavy to do them all manually one by one. Is there a tool to do this automatically (covert from PST/POP3 to Exchange 2003 mailboxes)? We will upgrade the users Office 2000/XP to Office 2003. TIA Linda On Mon, 30 May 2005 13:50:24 +0200, Linda Lindsez <spamblooocker@hotmail.com> wrote: >We are about to convert our users from PST files (POP3) to Exchange >2003 mailboxes. As there are several hundred of users, this would be >way to...

how many characters i can put into one cell in Excel
If i typed a whole paragraph into one cell of Excel, only a part of para show up in that cell. I tried wrap text, increase the height of the column. Nothing happen. Any one have idea about it or there are some limit to view in Excel in one cell Hi Search for 'specifications' in Help: It says '32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.' -- Andy. "Gary" <Gary@discussions.microsoft.com> wrote in message news:747E6CFE-88D9-4F4C-950E-56F0885C3096@microsoft.com... > If i typed a whole paragraph into one cell o...

Conditional Opening of Excel file
Hi, I have a complex workbook that relies on various SUMIF's from othe reference workbooks. If these aren't open, then obviously the formula don't work. As it's for someone else; I want a piece of VB so that when they *open the main workbook, it checks to see if the ref file is open, and i not, it automatically opens it for them. A bit like this sub worksheetopen '(Not sure where this should go in parent book) if workbook("Referencefile.xls") is NOT open then workbook("referencefile.xls").open endif endsub Any ideas folks?:confused -- Message p...

Suppressing zeros after the decimal point
How can I force zeros to display following a decimal point in Excel 2002? Whenever I type a number, such as 3.200, Excel drops the zeros and only enters 3.2 into the cell. Is there a setting that can be changed to fix this? (Using the "0.00" custom format seems to help, but if I have another number, such as 4.70, which only needs one zero to display, then I need another custom format for that one, and the next one, etc.) If anyone has a simpler suggestion, I'd appreciate it. Thanks! Hi AFAIK you can't achieve this with a custom format. Either you have three decimals or...

Is there an array formula that works with text?
The array formula {=3DSUM(IF(A1:A10=3D1,B1:B10))} will return the contents of B2 if A2=3D1 and the content of B2=3D a number. But what if B2=3D text? Can this formula, or one similar to it, be made to return a letter or word=97something other than a number? VLOOKUP or INDEX/MATCH would spring to mind Try Debra's nice coverage on these functions at her: http://www.contextures.com/xlFunctions02.html VLOOKUP http://www.contextures.com/xlFunctions03.html INDEX/MATCH There's also some sample workbooks available for d/l & study -- Max Singapore http://savefile.com/projects/236895 xdem...

Creating the occurrence frequency chart in Excel
I had a need to put a series of numbers into buckets of size 5. I created the following formula, and "dragged it down" =A1 + IF(MOD(A1,5), 5-MOD(A1,5), 0) I am sure that there is a better way of doing this. Can someone suggest it? Also, I wanted to come up with an occurrence frequency chart for my data. For example if I have numbers 4, 9, 13, 13, 17, 19, 19, 19, then I would like the occurrence frequency chart to plot the following informatiuon in a 2D chart: 0-4: 1 5-9: 2 10-14:2 15-30: 3 Thanks, Song Hi: This will work out the buckets for you. Paste it into the first cel...

access / excel link
i know that i can link excel tables to an access database. can i link an access table to a spreadsheet? -- TomMurray hi Tom, tom wrote: > i know that i can link excel tables to an access database. can i link an > access table to a spreadsheet? You may use Data/Get External Data, but this not the same as linked table. Depending on your needs, you may consider using a VBA procedure (macro) to read the data with a DAO.Recordset and copy it manually into to your cells - you can copy also a complete recordset, see CopyFromRecordset in Excel. mfG --> stefan <-- thanks stefa...

excel charts look drastically different on different computers
When we open excel charts on different computers in our office the formatting looks drastically different (for example, data labels and arrows on charts are in different locations). This is a huge problem because it means that the formatting has to be fixed each time someone opens an excel chart. This was never a problem until recently when some of our staff got new computers and for a short time they were using Office 2007 (everyone else in the office was using Office 2003). We've switched everyone back to Office 2003, but the problem with excel charts persists. When u ad...

Excel 2003
Whenever a colleague updates a spreadsheet I've created it comes back 10MBytes large whereas it was originally only 50KBytes. It looks to me as if the colleague accidentally adds tens or hundreds of thousands of empty rows. How can I delete all rows below a certain row. Delete does not work. Copying the real rows into a new worksheet loses the formatting. When I apply the formatting from the big spreadsheet (paste formatting) I end up with hundreds of thousands of rows again :-( Any suggestions? Tom Selecting all the rows below your actual data. Right-click, delete cells. (this is...

How do I assign a shortcut key to an existing macro in Excel 2003.
ntahall, Alt+F8 to bring up the choose macro box, select your macro, options, assign a shortcut from there -- 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 2000 & 2003 ** remove news from my email address to reply by email ** "ntahall" <ntahall@discussions.microsoft.com> wrote in message news:21B6102C-4B88-4131-A9AC-C48879896A17@microsoft.com... > ...

Default Line Weight in Excel
How do I change the default setting for the "Line Weight" when creating charts - at the moment all of my graphs are created with a very thin line weight & I would like the standard to be thicker.... You can't change the default line weight. However, you can create a custom chart with the heavier line weights and then use that custom chart instead of Excel's defaults. To do so, click once on your finished chart with the heavier line weights. Go to the standard toolbar and select Chart -> Chart Type. In the Chart Type dialog box, select the Custom Types tab. ...

How do I group text to graphic?
In my document I'm trying to embed a graphic into the text. How do I do this to where when I delete text before the graphic it moves with text instead of me having to redo embedding? BeanieBrain <BeanieBrain@discussions.microsoft.com> was very recently heard to utter: > In my document I'm trying to embed a graphic into the text. How do I > do this to where when I delete text before the graphic it moves with > text instead of me having to redo embedding? Which version of Publisher are you using? -- Ed Bennett - MVP Microsoft Publisher i'm using both 2000 and...

a sample question about excel VBA
How can I make msgbox like this? �� there are X sheets of this workbook the first page name is : Sheets(1).Name the secod page name is : Sheets(2).Name �� until to the last page the X page mane is : Sheets(X).Name How can I write this module? Sub WorksheetNames() Dim i As Long Dim strAnswer As String strAnswer = _ "There are " & Worksheets.Count & _ " sheets of this workbook" For i = 1 To Worksheets.Count strAnswer = strAnswer & vbCr & _ "Sheet " & i & " : " & _...

Convert Number To Indian Rupees
Dear Friends, Can anyone clearly suggest me how to convert the Number to Text . I need to convert the Number to Indian Ruppes . Eg :1. i need to convert : 1,57,50,178.10 as Rupees One Crore Fifty Seven Lacs Fifty Thousand One Seventy Eight and Ten Paisa Or One Crore Fifty Seven Lacs Fifty Thousand One Seventy Eight Rupees and Ten Paisa Eg 2.: 1,57,50,178.00 as Rupees One Crore Fifty Seven Lacs Fifty Thousand One Seventy Eight . OR One Crore Fifty Seven Lacs Fifty Thousand One Seventy Eight Rupees and Ten Paisa Pls Give me the programme that i can copy and paste to V.B.Module And run i...

Excel Menus Greyed out
This only happens in Excel XP and a re-install and/or repair don't seem to fix it. All other Office apps are fine. Anyone seen this? Remedy?! ...

convert numbers to words
Hi all, simple question, how to convert number to words. ex. $ 10,000 ==> ten thousand dollars thanks hi see this site.... http://support.microsoft.com/?kbid=213360 and this site.... http://www.sulprobil.com/html/spellnumber.html and this site.... http://www.ozgrid.com/VBA/ValueToWords.htm yeah....it can get complicated. good luck. regard FSt1 "reza" wrote: > Hi all, > > simple question, how to convert number to words. > ex. $ 10,000 ==> ten thousand dollars > > thanks FSt1... Thanks "FSt1" wrote: > h...

Excel cannot open email program.
I often use "file.. send to... mail recipient" to send a spreadsheet as text in the body of an e-mail. I prefer that to sending excel as an attachment, since my recipients may not have excel and usually don't need to see formulas. On my new computer however I get the message "excel could not open the e-mail program" if I try the first option above. Both excel 2002 and outlook 2002 are working fine. I "repaired" office just to be sure. Still doesn't work. Only difference is that the computer came with a trial version of office 2003 which I don...

Open XML File Converter
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Any word on an update to this converter that resolves the "There is not enough memory or disk space to convert this document" bug on SL? I've seen it reported elsewhere so I'm assuming it's a known issue, but can't find any relevant info here. Thanks. On 11/2/09 2:33 PM, in article 59b7fd3a.-1@webcrossing.caR9absDaxw, "elliotcolbert@officeformac.com" <elliotcolbert@officeformac.com> wrote: > Any word on an update to this converter that resolves the "There is not e...

GETPIVOTDATA and Date-type field (Excel 2007)
Data Sample *************** Trip 0346210980 Statement Date 3/24/2010 Description STARBUCKS Date 03/08/2010 LocCurrAmt 3.47 Formula: =GETPIVOTDATA("LocCurrAmt",A6,"Statement Date",DATE(2010,3,24)) Problem: I keep getting a #REF error. When I evaluate the error it's always with the DATE function. Steps I've taken: 1. Confirmed that my date is a number and not text. 2. Tried using the date serial number (40261) instead of the DATE function. 3. Confirmed the GETPIVOTDATA works...

why can I not format a secondary axis in excel x?
Despite being able to graph data on a secondary axis in Microsoft Excel X, I have been unable to format said axis (ie make it start from a value other than zero). No amount of trying to click on and select the axis or the values has helped. Are you saying that double clicking does not bring up the Format dialog box for the axis? Try tapping the 'navigation (arrow) keys while watching the Name box. When it reads Secondary Value.... use the Format menu item to open the dialog box and then open the Scale tab to set the Min and Max values best wishes Bernard "mackie99" <...

Delegate constructor compiles in VB.NET, but not in Excel VBA
I have created a DLL callback through a delegate function. It works fine when the client is C#, or VB.NET. I can't get it to compile in Excel VBA. I have this problem both in Excel 2003 and Excel 2007. The DLL references setting is ok, because when I take the reference out the compiler errors out on RemoteProcess. What am I missing? The snippets below are the dll code, the working vb.net client code, and the failing vba code. =========================================== The remoteprocess class is compiled as dll: =========================================== Public Cl...

How can I type more than one line of text into a cell in Excel?
I am using Excel to organize contact information, names, addresses, phone numbers, etc. I would like all of this information in one cell and in the standard format of: name address phone etc. I do not know how to make Excel accept more than one line of text, unless I cut and paste it from a Word document. Could someone please tell me how to format the cells so that more than one line of text can be accepted in a cell? Hi use ALT+ENTER for inserting line breaks -- Regards Frank Kabel Frankfurt, Germany "watermark" <watermark@discussions.microsoft.com> schrieb im Newsb...

Percentage calculation on text columns
I have a sheet that represents exam candidates, their grades over 3 assignments with an average score, in the final COLUMN there is an indication of whether they have passed or failed in the format "Pass" "Fail". I need a formula that will count the overall group and give a percentage of the group that has failed. Can anyone help? -- Connor ------------------------------------------------------------------------ Connor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27970 View this thread: http://www.excelforum.com/showthread.php?threadid=4...

Excel Crash
Multiple users here have this same problem that whenever they open a particular Excel file, it gives them a "Send Report Error." Weird thing about it is it doesnt happen in all their Excel files, only happens to some of them. Also tried opening the very same file/s they are having problems with on my machine and it worked fine. I have patched Office (SP2), updated Acrobat Reader, updated signatures, uninstalled/reinstalled Office, and done all the possible troubleshooting on these machines but still error occurs. Any help appreciated, thanks! Isolate the problem as best yo...

Font size changes in Excel 2003 charts when re-opened
For certain charts that I create within Excel 2003 (usually bar charts), I use a font size of 8 (eg for data labels, axis labels, and scale). If I close the file and then re-open it, all these fonts will have become size one (title and legend are not effected). Oddly, if I have created a bar chart and made several copies of it within a worksheet, it will only be the original and not the copies that are affected by this. If anyone can shed any light on why this may be happening and how it could be fixed, I would really appreciate it. Lucie ...