 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / May 2008Change Uppercase to Lowercase
I have a spread sheet created with Excel 2007. One colum has first and last names in all caps. How can I convert the first letter for each name to be caps and the rest of the letters to be lowercase? I want to do the whole colum at once. Thanks  Signature---------------------------------------------------- This mailbox protected from unsolicited email by Spam Alarm from Dignity Software http://www.dignitysoftware.com One way Sub makeallproper() mc = "c" lr = Cells(Rows.Count, mc).End(xlUp).Row Range(Cells(1, mc), Cells(lr, mc)).Value = _ Application.Proper(Range(Cells(1, mc), Cells(lr, mc)).Value) End Sub SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >I have a spread sheet created with Excel 2007. One colum has first and last >names in all caps. How can I convert the first letter for each name to be >caps and the rest of the letters to be lowercase? I want to do the whole >colum at once. > > Thanks The worksheet function "Proper" capitalizes the first letter of each string and makes the rest lower case. In your example, I am guessing that it should work fine, though you need to check it closely. Sometimes the last name has more than one part, e.g. in Ludwig van Beethoven has the "van" all in lower case. There are likely other cases, perhaps Asian surnames (though none come to mind) where it will give you an incorrect result; i.e. just what you asked for but not what you need. So use it judiciously. >I have a spread sheet created with Excel 2007. One colum has first and last >names in all caps. How can I convert the first letter for each name to be >caps and the rest of the letters to be lowercase? I want to do the whole >colum at once. > > Thanks Is this right? =proper(B4:B40) Signature---------------------------------------------------- This mailbox protected from unsolicited email by Spam Alarm from Dignity Software http://www.dignitysoftware.com > The worksheet function "Proper" capitalizes the first letter of each > string and makes the rest lower case. In your example, I am guessing that [quoted text clipped - 11 lines] >> >> Thanks no SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Is this right? > =proper(B4:B40) [quoted text clipped - 14 lines] >>> >>> Thanks No. You might have in, say, cell C4 =proper(b4) then in cell C5 =proper(b5), etc; i.e. you copy the formula to a parallel column with formulas matching each cell. Depending upon the application, you might want to use the VBA approach that Don Guillett showed you, but if you simply want to quickly convert the information using a formula in a worksheet would prove faster. If you then want to change the results from formulas to text, perhaps to email someone or for use in another application, you could highlight the cells with the formulas, and then choose from the menu: edit | copy | followed by edit | pastespecial | values I do this so often that I hardly can remember the menu commands, I just remember alt-e,c then alt-e,s,v > Is this right? > =proper(B4:B40) [quoted text clipped - 14 lines] >>> >>> Thanks Here is an other way to deal with selected cells anywhere in the worksheet.. ' Force explicit variable declaration Option Explicit Sub PROPER() ' Variable for selected cells Dim Cell ' Change all cells in the current selection For Each Cell In Selection Cell.Value = Application.WorksheetFunction.PROPER(Cell.Value) Next End Sub > I have a spread sheet created with Excel 2007. One colum has first and > last names in all caps. How can I convert the first letter for each name > to be caps and the rest of the letters to be lowercase? I want to do the > whole colum at once. > > Thanks Just a warning here........... If you have any formulas in your selected range, the posted code will change those formulas to values. Perferable is to leave them alone. Sub Proper() Dim Cell As Range Application.ScreenUpdating = False For Each Cell In Selection Cell.Formula = Application.Proper(Cell.Formula) Next Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP >Here is an other way to deal with selected cells anywhere in the worksheet.. > [quoted text clipped - 18 lines] >> >> Thanks Gord, Would this amendment get around that probelm or have I just swapped one error for another? Sub Proper() Dim Cell As Range Application.ScreenUpdating = False For Each Cell In Selection If Not Cell.HasFormula Then Cell.Value = Application.Proper(Cell.Value) End If Next Application.ScreenUpdating = True End Sub In my limited testting it seems to work. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Just a warning here........... > [quoted text clipped - 42 lines] >>> >>> Thanks That is another routine that I have posted in the past. Works fine for me and I have not run into any problems with it. Gord >Gord, > [quoted text clipped - 13 lines] > >In my limited testting it seems to work.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.