Sentence with first letter in Capital, rest all in small.

S

Shrikant

How to convert (from all caps sentence) the first letter of the sententence
to Capital and the rest all in small - just like we write ordinary English
language. The 'Proper( )' function converts to Capital the first letter of
each word in the sentence. This does not serve the purpose.
 
S

Sukhjeet

Shrikant
Use this formula in B1, assuming the sentence to be converted is in A1
=UPPER(LEFT(A1))&LOWER(MID(A1,2,LEN(A1)))
Sukhjeet
 
B

Bob Phillips

Here is a UDF

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

para = LCase(para)
Set oRegExp = CreateObject("VBScript.RegExp")
oRegExp.Pattern = "^[a-z]|\.( )*[a-z]"
oRegExp.Global = True
Set oMatches = oRegExp.Execute(para)
For Each oMatch In oMatches
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


Use like

=SentenceCase(A1)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top