Thank you Gord,

I'll give it a go!

Cheers,

Cecile.

-----Original Message-----
Cecile

A UDF and a macro that references it.............

Function CapFirst(ByVal str As String) As String
Dim aRegExp As Object, aMatch As Object, allMatches As
Object
Set aRegExp = CreateObject("vbscript.regexp")
aRegExp.Pattern = "^[a-z]|\.( )*[a-z]"
aRegExp.Global = True
Set allMatches = aRegExp.Execute(str)
For Each aMatch In allMatches
With aMatch
Mid(str, .firstindex + 1 + .length - 1, 1) = _
UCase(Mid(str, .firstindex + 1 + .length - 1,
1))
End With
Next aMatch
CapFirst = str
End Function

Sub Sentence_Case()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then Exit Sub
myStr = cel.Value
cel.Value = "=CapFirst(" & """" & myStr & """"
& ")"
cel.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Next cel
End Sub


Gord

On Tue, 14 Dec 2004 02:53:02 -0800, "Southcecile"
wrote:

Hi all, thanks for your replies, I will definitely give
them a try tomorrow.

Yes Gord I'd be interested in using a macro, sentence
case is what I'm after.

Cheers,

Cecile.

"Cecile" wrote:

Hi all, I have a spreadsheet (1000+ lines) which has a
lot
of upper cases. How can I change the text to sentence
case?
I tried looking at the PROPER function, but it doesn't
seem to do what I want.
Thanks in advance,
Cecile.


.