 |
 | 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 / General Excel Questions / May 2008Changing from upper case to lower case
Hi all I can't believe I can't remember how to do this but how do you change text in Excel from upper case to sentence case? I have a workbook with approx. 15 worksheets and everything is in upper case and needs changing. Thank you. Louise Use =LOWER(A1) to change to lower case or =PROPER(A1) to make the leading letter capital edvwvw >Hi all > [quoted text clipped - 4 lines] >Thank you. >Louise Depending on what you have in your sheets, (it will choke on Pivot tables etc.) trysomething like: Sub ChangeCase() Application.ScreenUpdating = False For Each sh In Worksheets sh.Activate For Each cell In Range("A1:M300") 'change A1:M300 to your range needs cell.Value = Application.Proper(cell.Value) Next cell Next sh Application.ScreenUpdating = True End Sub  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 > Hi all > [quoted text clipped - 5 lines] > Thank you. > Louise Sandy, Be careful with that one, it will change you formula to values. Mike > Depending on what you have in your sheets, (it will choke on Pivot tables > etc.) trysomething like: [quoted text clipped - 20 lines] > > Thank you. > > Louise Thanks for the catch Mike - I didn't have any formulas in the W/B I tested it on but the *Cell.Value* should have alerted me to the danger. SignatureSandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Sandy, > [quoted text clipped - 28 lines] >> > Thank you. >> > Louise Put this in a general module and run it Sub change_Case() For x = 1 To Worksheets.Count Worksheets(x).Select Selection.SpecialCells(xlCellTypeConstants, 2).Select For Each c In Selection c.Value = LCase(c.Value) Next Next End Sub Mike > Hi all > [quoted text clipped - 4 lines] > Thank you. > Louise Hi Mike Thanks very much for this, it works, however, is there any way it can capitalise the first letter of every word, as the worksheets contain people's names and divisions. Thanks again. Louise > Put this in a general module and run it > [quoted text clipped - 18 lines] > > Thank you. > > Louise Hi, Change this c.Value = LCase(c.Value) to this c.Value = WorksheetFunction.Proper(c.Value) Mike > Hi Mike > [quoted text clipped - 28 lines] > > > Thank you. > > > Louise That seems to have worked a treat and has saved me loads of time!! I still struggle to get my head around VBA and can't actually write it myself. Thanks very much. Louise > Hi, > [quoted text clipped - 39 lines] > > > > Thank you. > > > > Louise Louise, Your welcome and thanks for the feedback Mike > That seems to have worked a treat and has saved me loads of time!! I still > struggle to get my head around VBA and can't actually write it myself. [quoted text clipped - 45 lines] > > > > > Thank you. > > > > > Louise Louise. Just be careful that there are no formulas in your range to change. They will be converted to values only. Preferable to use c.Formula = WorksheetFunction.Proper(c.Formula) Gord Dibben MS Excel MVP >That seems to have worked a treat and has saved me loads of time!! I still >struggle to get my head around VBA and can't actually write it myself. [quoted text clipped - 45 lines] >> > > > Thank you. >> > > > Louise Gord, the code I suplied won't change formula to values. Mike > Louise. > [quoted text clipped - 55 lines] > >> > > > Thank you. > >> > > > Louise > Hi all > [quoted text clipped - 5 lines] > Thank you. > Louise Excel offers only - =lower(a1) =upper(a1) =proper(a1) (which capitalises everything) The only way I have found to change to 'sentence case' is to - copy and paste into Word use <Format><Change Case><Sentence Case> copy and past back into Excel This may be a bit tedious and there may be other ways but it works! Bill Ridgeway Computer Solutions Hi BIll Thanks for your reply, however, I have copied the macro Mike created and it works perfectly and takes a second to run!! If you do this quite often, it might be worth copying the macro too. Louise > > Hi all > > [quoted text clipped - 20 lines] > Bill Ridgeway > Computer Solutions |
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.