Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alter text
How would I alter text in every cell in a single column so that the wor is all lowercase except for the first letter which will be capitalized. Would I require an add-in for this? Thanks, Andre -- Excel_hel ----------------------------------------------------------------------- Excel_help's Profile: http://www.excelforum.com/member.php...fo&userid=3643 View this thread: http://www.excelforum.com/showthread.php?threadid=56630 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alter text
By Formula assuming the data is in column A.......................
In an adjacent column enter =PROPER(A1) Double-click on fill handle to copy down. Copy/paste specialValues then delete original column A With a macro....................... 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 MS Excel MVP On Sat, 29 Jul 2006 13:23:06 -0400, Excel_help wrote: How would I alter text in every cell in a single column so that the word is all lowercase except for the first letter which will be capitalized. Would I require an add-in for this? Thanks, Andrew |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alter text
If you have more than one word, the formula would be
=Ucase(Left(A1,1)) & lcase(Right(A1,len(A1)-1)) Do you want this to happen when a user enters data in that column? -- Regards, Tom Ogilvy "Excel_help" wrote in message ... How would I alter text in every cell in a single column so that the word is all lowercase except for the first letter which will be capitalized. Would I require an add-in for this? Thanks, Andrew -- Excel_help ------------------------------------------------------------------------ Excel_help's Profile: http://www.excelforum.com/member.php...o&userid=36439 View this thread: http://www.excelforum.com/showthread...hreadid=566301 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alter text
Tom Ogilvy Wrote: If you have more than one word, the formula would be =Ucase(Left(A1,1)) & lcase(Right(A1,len(A1)-1)) Do you want this to happen when a user enters data in that column? NO. The text is already there. Thanks. You've been a great help. Much appreciated! -- Excel_help ------------------------------------------------------------------------ Excel_help's Profile: http://www.excelforum.com/member.php...o&userid=36439 View this thread: http://www.excelforum.com/showthread...hreadid=566301 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
alter column ref | Excel Discussion (Misc queries) | |||
Alter Text in 900 cell entries | Excel Worksheet Functions | |||
How can I alter this Macro? | Excel Programming | |||
how to alter cell format if cell contains text | Excel Worksheet Functions | |||
How can I alter the size of the text box in an Excel | Charts and Charting in Excel |