change case

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 :)
 
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
 
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
 
thanks PCLIVE, very helpful and much appreciated for the explaination
10s
community as well :)
 
10s Gord Dibben, for the proper term to use for the "Proper" instead of
Sentence ..
:)
appreciated

10s community as well
 
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
 
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
 
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).
 
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).
 
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.
 
Back
Top