View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default changing text case

Excel supports conversion to upper, lower or proper case, but it has no
sentence case. I created this UDF to provide this

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 Phillips

(remove nothere from the email address if mailing direct)

"Linda B" wrote in message
...
Is there a way to change the text case in Excel?

Ex. from all caps to Sentence case, etc.