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

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

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

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