Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
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 :) -- oldLearner57 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
Have a look at the proper() and lower() functions.
"oldLearner57" wrote: 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 :) -- oldLearner57 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
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 "oldLearner57" wrote in message ... 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 :) -- oldLearner57 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
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 On Tue, 29 May 2007 06:49:00 -0700, oldLearner57 wrote: 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 :) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
10s :) toni.gee
much appreciated thanks community as well :) -- oldLearner57 "toni.gee" wrote: Have a look at the proper() and lower() functions. "oldLearner57" wrote: 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 :) -- oldLearner57 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
thanks PCLIVE, very helpful and much appreciated for the explaination
10s community as well :) -- oldLearner57 "PCLIVE" wrote: 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 "oldLearner57" wrote in message ... 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 :) -- oldLearner57 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
10s Gord Dibben, for the proper term to use for the "Proper" instead of
Sentence .. :) appreciated 10s community as well -- oldLearner57 "Gord Dibben" wrote: 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 On Tue, 29 May 2007 06:49:00 -0700, oldLearner57 wrote: 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 :) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
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. -- 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
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 "oldLearner57" wrote: 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 :) -- oldLearner57 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
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 "PlayingToAudienceOfOne" wrote: 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 "oldLearner57" wrote: 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 :) -- oldLearner57 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
10s David McRitchie
will do that thanks community :) -- oldLearner57 "David McRitchie" wrote: 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. -- 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
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). "oldLearner57" wrote: 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 "PlayingToAudienceOfOne" wrote: 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 "oldLearner57" wrote: 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 :) -- oldLearner57 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
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 InsertModule. 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). "oldLearner57" wrote: 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 "PlayingToAudienceOfOne" wrote: 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 "oldLearner57" wrote: 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 :) -- oldLearner57 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
thanks :)
much appreciated thanks community as well -- oldLearner57 "PlayingToAudienceOfOne" wrote: 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 InsertModule. 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). "oldLearner57" wrote: 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 "PlayingToAudienceOfOne" wrote: 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 "oldLearner57" wrote: 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 :) -- oldLearner57 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
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" ... 10s David McRitchie |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
change case
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. -- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm "PlayingToAudienceOfOne" m wrote in Case "S": ocell = UCase(Left(ocell.Text, 1)) & _ LCase(Right(ocell.Text, Len(ocell.Text) - 1)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change mixed case to upper case in Excel for all cells | Excel Discussion (Misc queries) | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) | |||
How do I change a column in Excel from upper case to lower case? | Excel Worksheet Functions | |||
How do I change existing text from lower case to upper case | Excel Discussion (Misc queries) | |||
How to use formula auditing to change upper case to Title Case. | Excel Worksheet Functions |