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

[Guest]

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.

 

[William]

Hi

Try...
=LEFT(A1,1)&LOWER(RIGHT(A1,LEN(A1)-1))


--


XL2003
Regards

William
(e-mail address removed)

 

[Guest]

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

 

[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)