Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is there a way to have a function auto correct words in ALL CAPS .
We have a database that maintains large amounts of data that are in all caps.
When that data is exported to Excel, we would like to be able to run something in the spreadsheet that decapitalizes the all caps into initial caps only. |
#2
|
|||
|
|||
Sub Proper_Case()
'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, 17 Nov 2004 11:29:08 -0800, "Kelly Seale" <Kelly wrote: We have a database that maintains large amounts of data that are in all caps. When that data is exported to Excel, we would like to be able to run something in the spreadsheet that decapitalizes the all caps into initial caps only. |
#3
|
|||
|
|||
Mr. Dibben,
Thank you for your time and assistance. It worked wonderfully. Regards, KdS "Gord Dibben" wrote: Sub Proper_Case() '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, 17 Nov 2004 11:29:08 -0800, "Kelly Seale" <Kelly wrote: We have a database that maintains large amounts of data that are in all caps. When that data is exported to Excel, we would like to be able to run something in the spreadsheet that decapitalizes the all caps into initial caps only. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
words and numbers in an IF function | Excel Discussion (Misc queries) |