Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Text (letter) To Number Excel | Excel Worksheet Functions | |||
numerical value of a letter | Excel Worksheet Functions | |||
how do I convert text in excel to 1st letter capital,only | Excel Worksheet Functions | |||
Change Capitals to Sentence Case | Excel Discussion (Misc queries) | |||
Have A Letter Reference A Name For An Intoduction | New Users to Excel |