Trying to convert uppercase to lowercase text

[Joe B]

Can someone help me? I am trying to convert uppercase
text to Sentence case. I have seen the "proper" commnd
but it doesn't seem to work. I must be doing something
wrong. I am on Excell 97.

Thanks.

 

[Paul B]

Joe, here is a macro that will do it

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

 

[drabbacs]

I'm not sure what sentence case is but you can use LOWER
to make the text lowercase.

Then, if sentence case means first word in sentences
capitalized, you could use SEARCH, RIGHT and MID to do
some manipulations.

 

[Paul B]

would have been better if I put the macro in!!
Sub TextConvert()
'By Ivan F Moala
'will change the text that you have selected,
'if no text is selected it will change the whole sheet
Dim ocell As Range
Dim Ans As String

Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")

If Ans = "" Then Exit Sub

For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(Ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub

 

[David McRitchie]

Sentence case is where the first letter of a sentence is capitalized
and after a period and space or two the first letter of the next sentence
is capitalized the remainer are lowercase, except for proper nouns,
but I doubt that you will see proper nouns being corrected. Here is
a solution for Sentence Case by Tushar Mehta.

Sentence_Case, Re: Use VBS RegExp to replace a-z with A-Z?,
Tushar Mehta, programming, 2002-08-04.
http://google.com/[email protected]

if you want to try to figure out Tushar's macro, take a look at some
information on Regular Expressions near the related area of
Extraction of a Group of Digits and Dashes, from postings by Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm#RegExp

For information on Getting started with macros (install and use) see
http://www.mvps.org/dmcritchie/excel/getstarted.htm