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

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.
--
Shrikant

Hi

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


--


XL2003
Regards

William



"Shrikant" wrote in message
...
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.
--
Shrikant

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

"Shrikant" wrote:

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.
--
Shrikant

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)


"Shrikant" wrote in message
...
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.
--
Shrikant