Thread: Proper Case
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips Bob Phillips is offline
external usenet poster
 
Posts: 10,593
Default Proper Case

Here is a UDF to do it

Private Function SentenceCase(ByVal para As String) As String
Dim oRegExp As Object
Dim oMatch As Object
Dim oAllMatches As Object

para = LCase(para)
Set oRegExp = CreateObject("VBScript.RegExp")
oRegExp.Pattern = "^[a-z]|\.( )*[a-z]"
oRegExp.Global = True
Set oAllMatches = oRegExp.Execute(para)
For Each oMatch In oAllMatches
With oMatch
Mid(para, .FirstIndex + 1 + .Length - 1, 1) = _
UCase(Mid(para, .FirstIndex + 1 + .Length - 1, 1))
End With
Next oMatch
SentenceCase = para
End Function


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Roger Bell" wrote in message
...
I know that you can use the =Proper formula for changing case from all
Capitals to Sentence. Is there a way that you can change the case from
Capitals to Sentence for a range of cells?
Thanks for any help