change caps to sentence case

[Guest]

How do I change cap letters to sentence case in columns within my excel spreadsheet?

 

[Gord Dibben]

Kay

One column at a time......

Enter =PROPER(A1) and drag/copy down.

Copy/paste special>values(in place) on the column then delete the original.

VBA Macro from David McRitchie.........

If unsure of what to do with this code, see David's Getting Started with
Macros and VBA.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Sub Proper()
'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

 

[Dave Peterson]

If there's only one sentence in the cell:

=UPPER(LEFT(A1,1))&LOWER(MID(A1,2,LEN(A1)))

But it soon becomes a pain looking for stuff that could end the sentence.

 

[David McRitchie]

Hi Kay,
First letter of each sentence capitalized and handles multiple sentences.

Sentence_Case, Re: Use VBS RegExp to replace a-z with A-Z?,
Tushar Mehta, programming, 2002-08-04.
http://google.com/[email protected]

You can find the above link in the Related area of my proper.htm page,
if you want to find it again.

 

[Dave Peterson]

I remembered that Tushar wrote a procedure, but couldn't find it.

Thanks,

 

[Debra Dalgleish]

Did you help him look for it? <g>

I remembered that Tushar wrote a procedure, but couldn't find it.

Thanks,

 

[Dave Peterson]

"Remember that there is a fine line between cool and irritating."

hehehe.

 

[Rowie]

Lower instead of Proper will change to lowercase then change the firs
lette

 

[Debra Dalgleish]

Ouch! Anyway, I crossed that line years ago.

Dana DeLouis posted a Sentence Case macro too:

http://groups.google.com/groups?&threadm=uozj4JzUBHA.2076@tkmsftngp03

"Remember that there is a fine line between cool and irritating."

hehehe.

 

[Ian Murdoch]

Hi,

You could try downloading the free utility to do it (and lot more
from www.asap-utilities.com

Rgds

Ian Murdoc