change case

[Guest]

hi community

can assist me to solve this ...

example:

A B C
1 living to Eat

2 Eat to LIVE

base on the above A1, I want to change the text to Sentence Case (Capitalise
the 1st lettering)

and in A2, I want to change the text to lower case

any help much appreciated

thanks community for the assistance

 

[Guest]

Have a look at the proper() and lower() functions.

 

[PCLIVE]

For A1, if you want the first letter of each word capitalized, then:
=PROPER(A1)

If you want to keep everthing the same and just capitalize the first letter
of the first word only, then one way might be:
=UPPER(LEFT(A1,1))&RIGHT(A1,LEN(A1)-1)


For A2:
=LOWER(A2)


HTH,
Paul

 

[Gord Dibben]

First letter of each word capitalized is Proper case, not sentence.

See help on PROPER, LOWER and UPPER case functions.


Gord Dibben MS Excel MVP

 

[Guest]

10s toni.gee

much appreciated

thanks community as well

 

[Guest]

thanks PCLIVE, very helpful and much appreciated for the explaination
10s
community as well

 

[Guest]

10s Gord Dibben, for the proper term to use for the "Proper" instead of
Sentence ..

appreciated

10s community as well

 

[David McRitchie]

Each of what you asked for can be found on my page
http://www.mvps.org/dmcritchie/excel/proper.htm

including Sentence case for multiple sentences, which I
didn't see anything posted, for that look for Mehta on the
page for links to code by Tushar Mehta.

 

[Guest]

Copy the following macro:
Sub Change_Case()
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

 

[Guest]

hi
very appreciated for the vba to do the changes,
being a novice in vba, is it possible to guide me in how to use??
did ever requested for this from community & got a very helpful respond
however i loss the page ;(
what i know are as follow:
1st - i insert a module withiin my workbook (inside vbe)
2nd - copied the code as provided
3rd - closed the vbe
4th - how then i can call out the vba code to execute what was written
in the worksheet ? (instead of going to the Macro dialog box ?)

much appreciated if can guide on this

thanks to community

-
oldLearner57

 

[Guest]

10s David McRitchie

will do that

thanks community

 

[Guest]

Forgot to tell you... to run the module, go to TOOLS MACRO and highlight the
macro "Module7.Change_Case" (by the way, it won't be Module7... it will
number automatically to whatever).

 

[Guest]

Copy the following macro:

Sub Change_Case()
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
CTRL+C (shortcut for Copy)
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press
the F11 key and release both). You are now in the Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module
sheet in front of you. Click in it and then press CTRL+V (same method (this a
shortcut for Paste). You should now see the text of the function in the
Module. Press ALT+F11 again to return to your Excel worksheet.

To run the module, first select (highlight) the data you want to change
case. Then go to TOOLS MACRO and highlight the macro titled
"Module7.Change_Case" (by the way, it probably won't be named Module7... it
will number sequentially to whatever).

 

[Guest]

thanks
much appreciated

thanks community as well

 

[David McRitchie]

You're welcome.
You later asked how to run the macro other than Tools, Macro,...
You can create a toolbar button, or a menu item, see
http://www.mvps.org/dmcritchie/excel/toolbars.htm
--
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"oldLearner57" ...

 

[David McRitchie]

A bit annoying to see the same incorrect macro code over and over
again in these newsgroups in reply to questions already correctly
answered and many of them more than five days old, and more than
once in this thread. The answer to sentence case can be correctly
seen Tushar Mehta's code previously mentioned in this thread.
And making someone reply to a question everytime they invoke
a macro for a single simple task is not efficient for productivity.

A1: this is sentence number one. this is
another sentence. so is this.