convert text case in same column or row?

How do I convert text case in the same colum or row.  At the moment I use 
UPPER, but have to create a new column/row.
0
siani (1)
4/4/2005 2:45:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1470 Views

Similar Articles

[PageSpeed] 3

You may find this to be handy
Sub ChangeCase() 'Don Guillett
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & "          L for
lower" & Chr$(13) & " Or " & Chr$(13) & "          P for Proper", "Select
Case  Desired"))
Select Case nCase
Case "L"
  For Each r In Selection.Cells
    If r.HasFormula Then
       r.Formula = LCase(r.Formula)
      'R.Formula = R.Value
    Else
       r.Value = LCase(r.Value)
    End If
  Next

Case "U"
For Each r In Selection.Cells
    If r.HasFormula Then
       r.Formula = UCase(r.Formula)
      'R.Formula = R.Value
    Else
       r.Value = UCase(r.Value)
    End If
  Next
Case "P"

For Each r In Selection.Cells
    If r.HasFormula Then
       r.Formula = Application.Proper(r.Formula)
      'R.Formula = R.Value
    Else
       r.Value = StrConv(r.Value, vbProperCase)
    End If
Next
End Select
Application.ScreenUpdating = True
End Sub


-- 
Don Guillett
SalesAid Software
donaldb@281.com
"siani" <siani@discussions.microsoft.com> wrote in message
news:8CA24A26-FEC6-48CF-9948-FCF7A8AB26FB@microsoft.com...
> How do I convert text case in the same colum or row.  At the moment I use
> UPPER, but have to create a new column/row.


0
Don
4/4/2005 2:47:09 PM
Reply:

Similar Artilces:

Can I move down 12 rows in excel for every new entry
I am entering records of numbers which have formulas for calculation. I want to place these records every 12 rows and be able to automate the worksheet for our office personnel. How? HELP!! There can be anywhere from 50 to 150 records depending on how many batches are produced in manufacturing plant. right click sheet tab>view code>copy\paste this. If not column A (1) then change. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 1 Then Exit Sub Target.Offset(12).Select End Sub -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Mickey&...

Help: Formula put into entire row creates large file size
Hi, I have a spreadsheet where I would like one column to be the concatenation of two other columns. For example, in column C I would have =A1&B1. This excel file will be given to people and they will fill in values into columns A and B. The trick is, I don't know how many rows there will be, so I don't know how many rows in column C should have this formula. If I paste this formula into the entire column C, the excel file blows up to 3 megabytes. (It's only 15KB without this column). Are there ways for me to accomplish this more efficiently? Thanks for any help Are y...

How do i intall grahpic converters?
My Clip Art has mysterysly disappeared.... I get a message "Publisher cannot insert this picture into the publication. The appropriate grahpic converter is not available. For information on running Setup to intall converteres, press F1". F1 does not bring anything up when I press it. What version of Publisher are you using? Maybe the following article will be useful: (822518) You cannot preview or insert images correctly when you use Web Collections to search the Internet for clip art thumbnails in Office 2003 http://support.microsoft.com/?kbid=822518 -- John Inzer &quo...

Ordering by number and text
I use a report to print out checklists that in the detail section have item number to delineate each checklist item. The item numbers as an example are 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is grouped by checklist section and the grouping works perfectly but when the report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9. Obviously I want the the order to be in proper numerical sequence where 1-10 comes after 1-9. but even replacing the '-' with a decimal point doesn't ...

Importing column in comma saparated values.
Hi, This is my first post here..:) I have some values in a column in excel and I want to have them i comma saparated values. The Outout can be in any type of file as lon as I can copy it..I am sure, the experts here can solve this and hel me a great deal...Please help... Thanks in advance. -- Message posted from http://www.ExcelForum.com You could File/Save As then select .CSV ??? --- Message posted from http://www.ExcelForum.com/ If it's just one column (out of many used columns), then I'd just copy and paste into NotePad and save from there. But I'm not quite sure how y...

last row or record
there is no ID in this table. table1: col1 col2 col3 ----- ------ ----- aaa bbb ddd aab jjj fff ccc qqq ppp like to get this output: col1 col2 col3 ----- ------ ----- ccc qqq ppp -- Message posted via http://www.accessmonster.com sorry to buttoning in between... try this... create form using table, and in form on load properties. DoCmd.GoToRecord , , acLast regards "igg via AccessMonster.com" wrote: > there is no ID in this table. > table1: > col1 col2 col3 > ----- ------ ----- > aa...

Frx NP rows and Excel export
In versions of Frx6.7 SP9 and lower when a column is made NP in Frx, when the report genrated is exported to Excel the NP column is not transferred to Excel. In Frx6.7 SP10 I have now found that NP columns now export to Excel and are displayed as hidden columns in Frx. As far as I can tell is is supposed to be function enhancement rather than a bug. Can someone confirm this for me? Also - is there a way somehow to ensure it works in the way it did previosuly (I ask this I my client has multiple complex Excel sheets that link to the columns in this exported sheet - now due to this '...

Converting XLS file to QIF or to OFX
How do I safely and securely convert an excel file (xls) to a QIF or OFX file? "dreamchaser" wrote: > How do I safely and securely convert an excel file (xls) to a QIF or OFX file? In Excel, save the file to CSV and ustilise iCreateOFX Basic from: http://icreateofx.co.uk/Convert-CSV-to-OFX to convert the saved CSV file to OFX. ...

help
I have some texts files, which i want to read in each line, and then write back each line to a new text file. So for example, I want to read in the 2 lines below: "C:\Data\=D3=EA=D2=B9=D0=C7=BF=D5",6,10,3,3,8 "",0,0,0,318.592,83.04552 I store each line of data in the following vector: std::vector<CString> FileData; So FileData[0] =3D ""C:\Data\=D3=EA=D2=B9=D0=C7=BF=D5",6,10,3,3,8" FileData[1] =3D """,0,0,0,318.592,83.04552" (the data is in the vector correctly) Then I go to write the data to a new text file... FILE ...

Upper Case to Title Case
My description field and extended description field are all in UPPER CASE. Is there a way to change the text to Title Case without editing each field? ...

Dynamic text
Hi, I would like to add a serial number to a publication I'm designing. Is there a way to get a different number (sequential or otherwise) on a publication? Thanks Brian W Mail merge, create a data file. Read the help files, it is the way to all knowledge. -- Mary Sauer MS MVP http://dgl.microsoft.com/ http://mvps.org/msauer/ "Brian W" <brianw@gold_death_2_spam_rush.com> wrote in message news:eFVmhdGUDHA.2008@TK2MSFTNGP11.phx.gbl... > Hi, > > I would like to add a serial number to a publication I'm designing. Is there > a way to get a different number...

Time Format to Text Output
Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 1...

Printing only non-blank rows
Hi all, 1) Is there a command I can use or a macro code so that I only print non-blank rows? 2) Similar problem: is there a command or macro so that I only print rows that do not contain error values? Thanks for any ideas Matthew --- Message posted from http://www.ExcelForum.com/ An alternative to VBA is to apply an Advanced Filter and filter for those rows that only contain data. Try this: 1. An empty cell below your data (say E100), put: =COUNTA(2:2)>0 where row 2 is the 1st row of data and row 1 = headers. 2. Select your data and go to Data > Filter > Advanced F...

Opening Text Files and Re-Saving Them
Hi. I am trying to write some code which will allow a user to select a folder and then will open all of the text files in that folder and re-save them as excel files. I have sorted out the code to enable the user to select the folder they wish to use, but am looking some assistance on the looping to enable each of the text files to be opened and re-saved. Can anyone suggest how I might do this. Thanks. If the text files are all the same, you could record a macro that Opens, saves as an excel file, and closes the file. Then that recorded macro could be modified to open...

How can i stop same data being repeated in a column
I have a list of contract numbers relating to application numbers or payments. they are in the format nnnnnnan or nnnnnnpn. The columns are fixed to this format only. If they are entered with the a or p in the wrong place or if they have been left out completely an error message will appear to alert the user. I want to know how to alert the user if they enter an application or a payment number that has already been entered. ie if they enter 022079a4 but that same application has been entered else where in the column. Hope you can help Ru Hello See Chip Pearson's http://www.cpearson....

Limiting The Amount of Text
Hi, Is there a way I can limit the amount of Text I can enter into th cells of a selected column? For example, when listing on eBay, the description bar on eBay i limited to 40 characters. Since I list items on eBay with Excel, i would save me a lot of time if I were able to lock the cells in m "Description" column to 40 characters instead of going back an counting all the characters in those cells. Is this doable ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum....

Convert Worksheet to Userform
I'm not sure if this would be beneficial, so I would need help to understand whether a userform is better than a spreadsheet for allowing individuals to enter data, and then performing calculations and generating a results page. Second, if the userform is the best way to go, is it difficult to covnert a spreadsheet (with some YES/NO option buttons and a list box and lots of data fields) to a userform without having to re-write alot of code? I'm using Excel 2000. Thanks. I think it's a resounding--It depends... But there is another option. Excel has Data|Form that might...

Can I convert footnote numbers to text
I'm working with a document where the author has used the automated endnote facility in Word. Since this is about to be translated into Quark for typesetting, I need the endnote marks and the numbers with the endnotes to be real numbers, not endnote marks -- Quark will not read them as numbers. I was sure I did the conversion using ActiveDocument.ConvertNumbersToText in Visual Basic once before, but I can't seem to make it work. Help! There are 20 chapters with about 40 endnotes each. ActiveDocument.ConvertNumbersToText only works with paragraph numbering (and LISTNUM fi...

Excel 2003 / Send To / Mail Recipient / body text is removed
When a user is working in Excel 2003 and hits File.. Send To.. Mail Recipient.. it takes the spreadsheet and places it in an Outlook 2003 email as an Attachment. If they type any text into the body of the email it is replaced with <<...>> How can I change this ? If you don't get a good reply here, you may want to post in one of the .Outlook newsgroups. jmaynard2 wrote: > > When a user is working in Excel 2003 and hits File.. Send To.. Mail > Recipient.. it takes the spreadsheet and places it in an Outlook 2003 email > as an Attachment. > > If they typ...

Wrap text in cell
It seems that wrap text and autofit row width and row height have a limit. Any idea where to change this limit? I have quite a large text in a cell and when I do wrap text and autofit, then parts of the text get cut of. The text is still there, but it is not shown in the cell and it is cut off when printing. You can enter 32767 characters in a cell. In Excel 2003 and earlier, only 1024 of these will be visible or printed. I believe Excel 2007 will show 8192 characters. If you add an Alt + Enter linefeed every 100 or so characters you can increase this 1024 limit. ...

Changing Font case in excel
Is there a way to to change upper case fonts to say, title case or lower case Thanks in advance Andrew Andrew, here is one way, with your data in A1 =PROPER(A1) =LOWER(A1) -- 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 97 & 2000 ** remove news from my email address to reply by email ** "Andrew S." <anonymous@discussions.microsoft.com> wrote in message news:D0F578F3-286B-478F-B7B8-2209F19F55F8@microsoft.com... > Is there a w...

Convert Decimal to Calendar Date
Hi all, I have tried to search in many places for this, but I usually find results for converting the other way round! What I want to do is to convert a decimal number representing months from now (say 8.7) to an actual calendar date (like 5 February 2010). I am working up a spreadsheet that shows how long it will take to pay my debts so the decimal figure will change frequently (as of course will todays date). I get the decimal from dividing total debt by monthly payment amount but it would be great to see an actual date. Could anyone please offer advice? Thanks. Andy. Does 8.7 mean 8.7...

Converting from Lotus 1, 2, 3 docs
I have a user who has a bunch of Lotus 1, 2, 3 spreadsheets that he is trying to convert to Excel. His version of Lotus is old (version 5), his version of Excel is 2000 sp2. The files convert but they are missing pages or headings or other unacceptable qualities. Anyone any advice? Michael If you open the files using Excel and save them to an .xls format in the current version you are losing data? A while back I needed to do this. I wrote an application in VB that loops through the files, opens them using Excel (add a reference in VB to MS Office 10) and then save them in the current ...

internal margin around text in a cell
Hi, all... Within a cell (or the spreadsheet as a whole), is there a way to specify the width of the "internal margin"? I know this can be set for text boxes (separately for top, bottom, right, left), but I have found no reference in Help for doing this in a cell... Where I use a double-line border above a cell, the top of the text actually touches the border. For one column of cells, "Wrap text" is checked, so effective row height is variable from one row to the next. So far, I've managed by manually adjusting each row height and formatting the text to be &q...

Rotating Text
I need to rotate text on my report to read sideways, bottom-to-top. I'm having trouble with the Stephen Lebans approach which I will describe below but first I'd like to ask the Microsoft reps out there if Microsoft is going to build something into MS Access to solve this problem or if they are going to continue to just ignore it? Many thanks to Stephen for developing a workaround solution. Hopefully I can get it to work. I am using Access 2003 on WinXP Pro. I've placed a label on my report with the Tahoma true-type font, Label104, with 90 in the Tag. I've also placed a...