If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Proper Function
Hi,
I have about 20 columns in an Excel spreadsheet that contain name, address, etc. in various forms of capitalization. I believe I can use the proper fucntion to capitalize the first letter of each work and make all other letters lowercase. My question is how do I do this for a series of columns, or selected columns all at one time. I see to be getting an error message related to a circular pattern or something like that Any help is much appreciated Thanks Bill |
#2
|
|||
|
|||
Proper Function
If Columns A through T are the columns you want to change, then you need
U1 to say: =proper(a1) Drag that column to the right for the remaining 19 columns and however far down the columns are. Then, to change your output from a formula to text, highlight all the formula columns (the ones with 'proper'), copy, paste special - values. Now you should be able to move them freely. --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Proper Function
To do 20 columns at a whack you need VBA.
If unsure what to do with this code, see David McRitchie's site for getting started with VBA. http://www.mvps.org/dmcritchie/excel/getstarted.htm Sub Proper_Casek() 'David McRitchie, programming, 2003-03-07 Dim rng1 As Range, rng2 As Range, bigrange As Range Dim cell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual On Error Resume Next Set rng1 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeConstants)) Set rng2 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeFormulas)) On Error GoTo 0 If rng1 Is Nothing Then Set bigrange = rng2 ElseIf rng2 Is Nothing Then Set bigrange = rng1 Else Set bigrange = Union(rng1, rng2) End If If bigrange Is Nothing Then MsgBox "All cells in range are EMPTY" GoTo done End If For Each cell In bigrange cell.Formula = Application.Proper(cell.Formula) Next cell done: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Sub optProper_Click() 'David McRitchie, programming, 2003-03-07 Dim rng1 As Range, rng2 As Range, bigrange As Range Dim cell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual On Error Resume Next Set rng1 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeConstants)) Set rng2 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeFormulas)) On Error GoTo 0 If rng1 Is Nothing Then Set bigrange = rng2 ElseIf rng2 Is Nothing Then Set bigrange = rng1 Else Set bigrange = Union(rng1, rng2) End If If bigrange Is Nothing Then MsgBox "All cells in range are EMPTY" GoTo done End If For Each cell In bigrange cell.Formula = Application.Proper(cell.Formula) Next cell done: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Gord Dibben Excel MVP On Wed, 7 Jul 2004 13:28:21 -0700, wrote: Hi, I have about 20 columns in an Excel spreadsheet that contain name, address, etc. in various forms of capitalization. I believe I can use the proper fucntion to capitalize the first letter of each work and make all other letters lowercase. My question is how do I do this for a series of columns, or selected columns all at one time. I see to be getting an error message related to a circular pattern or something like that Any help is much appreciated Thanks Bill |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Text Function Proper | Kelly | Worksheet Functions | 2 | February 18th, 2004 08:58 PM |