|  | Changing Text Case | | |
| | | Jan :) |  |
|  Posted: Wed Jun 18, 2008 11:32 am Post subject: Changing Text Case |  |
Hi all, Excel 2007:
How can I change a range of text to Proper in place? Whenever I try to
change the text in a specific range, it will not change the text in that
range, it places it in some other area of the spreadsheet. I want to change
the text from Upper case to Proper case in B4:B15, but, no matter where I
place the cursor, or how I try to get the text to change in those cells, it
does not does not change the text in those cells. If I select the range and
then select the PROPER in the Text list, it puts the function in the first
cell of the column, but, does not change the case of the text in those
cells. So, what am I doing wrong? How would I do this for a range of
several columns?
Jan  |
| |
| | | Gary''s Student | |
| | Posted: Wed Jun 18, 2008 2:57 pm Post subject: RE: Changing Text Case | |
Select the cells you want to convert and run this macro:
Sub fixit()
For Each r In Selection
r.Value = Application.WorksheetFunction.Proper(r.Value)
Next
End Sub
--
Gary''s Student - gsnu2007j
"Jan " wrote:
| Quote: | Hi all, Excel 2007:
How can I change a range of text to Proper in place? Whenever I try to
change the text in a specific range, it will not change the text in that
range, it places it in some other area of the spreadsheet. I want to change
the text from Upper case to Proper case in B4:B15, but, no matter where I
place the cursor, or how I try to get the text to change in those cells, it
does not does not change the text in those cells. If I select the range and
then select the PROPER in the Text list, it puts the function in the first
cell of the column, but, does not change the case of the text in those
cells. So, what am I doing wrong? How would I do this for a range of
several columns?
Jan :)
|
|
| |
| | | Jan :) | |
| | Posted: Wed Jun 18, 2008 7:10 pm Post subject: Re: Changing Text Case | |
| |  | |
"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:C0B7741E-D918-46FE-B617-EF30969B1DEA@microsoft.com...
| Quote: | Select the cells you want to convert and run this macro:
Sub fixit()
For Each r In Selection
r.Value = Application.WorksheetFunction.Proper(r.Value)
Next
End Sub
|
Thanks, Gary. I take it there is no means to do this as a built-in function
in Excel? The reason I ask is that I was asked to show some of my
co-workers how to do this using a built-in function, like the Change Case or
Change Text Case function in Word if there was one. But, I'll see if I can
create a Macro they can add to their Quick toolbar.
Thank you for your time and help.
Jan :)
| Quote: |
--
Gary''s Student - gsnu2007j
"Jan " wrote:
Hi all, Excel 2007:
How can I change a range of text to Proper in place? Whenever I try to
change the text in a specific range, it will not change the text in that
range, it places it in some other area of the spreadsheet. I want to
change
the text from Upper case to Proper case in B4:B15, but, no matter where I
place the cursor, or how I try to get the text to change in those cells,
it
does not does not change the text in those cells. If I select the range
and
then select the PROPER in the Text list, it puts the function in the
first
cell of the column, but, does not change the case of the text in those
cells. So, what am I doing wrong? How would I do this for a range of
several columns?
Jan :)
|
|
| |
| | | Jan :) | |
| | Posted: Wed Jun 18, 2008 7:35 pm Post subject: Re: Changing Text Case | |
| | | |
Hi Gary....
That did work ok fine. What Value would I use other than the r to be able
to create a Macro for the Upper and Lower as well?
Jan :)
"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:C0B7741E-D918-46FE-B617-EF30969B1DEA@microsoft.com...
| Quote: | Select the cells you want to convert and run this macro:
Sub fixit()
For Each r In Selection
r.Value = Application.WorksheetFunction.Proper(r.Value)
Next
End Sub
--
Gary''s Student - gsnu2007j
"Jan " wrote:
Hi all, Excel 2007:
How can I change a range of text to Proper in place? Whenever I try to
change the text in a specific range, it will not change the text in that
range, it places it in some other area of the spreadsheet. I want to
change
the text from Upper case to Proper case in B4:B15, but, no matter where I
place the cursor, or how I try to get the text to change in those cells,
it
does not does not change the text in those cells. If I select the range
and
then select the PROPER in the Text list, it puts the function in the
first
cell of the column, but, does not change the case of the text in those
cells. So, what am I doing wrong? How would I do this for a range of
several columns?
Jan :)
|
|
| |
| | | Jan :) | |
| | Posted: Wed Jun 18, 2008 7:45 pm Post subject: Re: Changing Text Case | |
| | | |
Is there a way to create a Macro that I can use for all workbooks? I know I
can copy them to another workbook, but, would like to be able to run the
Macros when needed in other workbooks as well.
Jan :)
"Jan " <abuse@localhost.invalid> wrote in message
news:OucTOfU0IHA.4572@TK2MSFTNGP03.phx.gbl...
| Quote: | Hi all, Excel 2007:
How can I change a range of text to Proper in place? Whenever I try to
change the text in a specific range, it will not change the text in that
range, it places it in some other area of the spreadsheet. I want to
change the text from Upper case to Proper case in B4:B15, but, no matter
where I place the cursor, or how I try to get the text to change in those
cells, it does not does not change the text in those cells. If I select
the range and then select the PROPER in the Text list, it puts the
function in the first cell of the column, but, does not change the case of
the text in those cells. So, what am I doing wrong? How would I do this
for a range of several columns?
Jan |
|
| |
| | | Gord Dibben | |
| | Posted: Wed Jun 18, 2008 8:00 pm Post subject: Re: Changing Text Case | |
| | | |
Note that the student's macro will wipe out formulas if there are any in the
range you selected.
These won't.
Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = UCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub
Sub Lower()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = LCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub
Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = Application.Proper(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub
Gord Dibben MS Excel MVP
On Wed, 18 Jun 2008 17:35:40 -0400, "Jan :\)" <abuse@localhost.invalid> wrote:
| Quote: | Hi Gary....
That did work ok fine. What Value would I use other than the r to be able
to create a Macro for the Upper and Lower as well?
Jan :)
"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:C0B7741E-D918-46FE-B617-EF30969B1DEA@microsoft.com...
Select the cells you want to convert and run this macro:
Sub fixit()
For Each r In Selection
r.Value = Application.WorksheetFunction.Proper(r.Value)
Next
End Sub
--
Gary''s Student - gsnu2007j
"Jan " wrote:
Hi all, Excel 2007:
How can I change a range of text to Proper in place? Whenever I try to
change the text in a specific range, it will not change the text in that
range, it places it in some other area of the spreadsheet. I want to
change
the text from Upper case to Proper case in B4:B15, but, no matter where I
place the cursor, or how I try to get the text to change in those cells,
it
does not does not change the text in those cells. If I select the range
and
then select the PROPER in the Text list, it puts the function in the
first
cell of the column, but, does not change the case of the text in those
cells. So, what am I doing wrong? How would I do this for a range of
several columns?
Jan :)
|
|
| |
| | | Gord Dibben | |
| | Posted: Wed Jun 18, 2008 8:02 pm Post subject: Re: Changing Text Case | |
| | | |
Place the code in your Personal.xls and it will be available for all workbooks.
Or download Chip Pearson's add-in.
LINK
Scroll down to Case Convert
Gord Dibben MS Excel MVP
On Wed, 18 Jun 2008 17:45:59 -0400, "Jan :\)" <abuse@localhost.invalid> wrote:
| Quote: | Is there a way to create a Macro that I can use for all workbooks? I know I
can copy them to another workbook, but, would like to be able to run the
Macros when needed in other workbooks as well.
Jan :)
"Jan " <abuse@localhost.invalid> wrote in message
news:OucTOfU0IHA.4572@TK2MSFTNGP03.phx.gbl...
Hi all, Excel 2007:
How can I change a range of text to Proper in place? Whenever I try to
change the text in a specific range, it will not change the text in that
range, it places it in some other area of the spreadsheet. I want to
change the text from Upper case to Proper case in B4:B15, but, no matter
where I place the cursor, or how I try to get the text to change in those
cells, it does not does not change the text in those cells. If I select
the range and then select the PROPER in the Text list, it puts the
function in the first cell of the column, but, does not change the case of
the text in those cells. So, what am I doing wrong? How would I do this
for a range of several columns?
Jan |
|
| |
| | | Jan :) | |
| | Posted: Wed Jun 18, 2008 8:45 pm Post subject: Re: Changing Text Case | |
| | | |
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:e91j54t1ls93la60es8bcousiiu6v87ind@4ax.com...
| Quote: | Note that the student's macro will wipe out formulas if there are any in
the
range you selected.
These won't.
Sub Upper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = UCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub
Sub Lower()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = LCase(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub
Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = Application.Proper(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub
Gord Dibben MS Excel MVP
|
Thank you very much, Gord. It is good that it will not delete any forumlas.
Is there a way that a Master Macro for each of these can be created that can
be Run with any workbook? Or, is that what the code is indicating?
Jan :)
| Quote: |
On Wed, 18 Jun 2008 17:35:40 -0400, "Jan :\)" <abuse@localhost.invalid
wrote:
Hi Gary....
That did work ok fine. What Value would I use other than the r to be able
to create a Macro for the Upper and Lower as well?
Jan :)
"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in
message
news:C0B7741E-D918-46FE-B617-EF30969B1DEA@microsoft.com...
Select the cells you want to convert and run this macro:
Sub fixit()
For Each r In Selection
r.Value = Application.WorksheetFunction.Proper(r.Value)
Next
End Sub
--
Gary''s Student - gsnu2007j
"Jan " wrote:
Hi all, Excel 2007:
How can I change a range of text to Proper in place? Whenever I try to
change the text in a specific range, it will not change the text in
that
range, it places it in some other area of the spreadsheet. I want to
change
the text from Upper case to Proper case in B4:B15, but, no matter where
I
place the cursor, or how I try to get the text to change in those
cells,
it
does not does not change the text in those cells. If I select the
range
and
then select the PROPER in the Text list, it puts the function in the
first
cell of the column, but, does not change the case of the text in those
cells. So, what am I doing wrong? How would I do this for a range of
several columns?
Jan :)
|
|
| |
| | | Jan :) | |
| | Posted: Wed Jun 18, 2008 8:47 pm Post subject: Re: Changing Text Case | |
| | | |
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:cc1j54luocnjpai58dlaqjjsctigsepi5a@4ax.com...
| Quote: | Place the code in your Personal.xls and it will be available for all
workbooks.
Or download Chip Pearson's add-in.
LINK
Scroll down to Case Convert
|
Ahhh...thank you very much. Disregard my other question on this to your
other reply. I did not see this reply at the time. I'll try the download
as well.
I truly do appreciate your time and help. :-)
Jan
| Quote: |
Gord Dibben MS Excel MVP
On Wed, 18 Jun 2008 17:45:59 -0400, "Jan :\)" <abuse@localhost.invalid
wrote:
Is there a way to create a Macro that I can use for all workbooks? I know
I
can copy them to another workbook, but, would like to be able to run the
Macros when needed in other workbooks as well.
Jan :)
"Jan " <abuse@localhost.invalid> wrote in message
news:OucTOfU0IHA.4572@TK2MSFTNGP03.phx.gbl...
Hi all, Excel 2007:
How can I change a range of text to Proper in place? Whenever I try to
change the text in a specific range, it will not change the text in that
range, it places it in some other area of the spreadsheet. I want to
change the text from Upper case to Proper case in B4:B15, but, no matter
where I place the cursor, or how I try to get the text to change in
those
cells, it does not does not change the text in those cells. If I select
the range and then select the PROPER in the Text list, it puts the
function in the first cell of the column, but, does not change the case
of
the text in those cells. So, what am I doing wrong? How would I do
this
for a range of several columns?
Jan
|
|
| |
| | | Gord Dibben | |
| | Posted: Wed Jun 18, 2008 8:58 pm Post subject: Re: Changing Text Case | |
| | | |
Glad to help.
Gord
On Wed, 18 Jun 2008 18:47:40 -0400, "Jan :\)" <abuse@localhost.invalid> wrote:
| Quote: |
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:cc1j54luocnjpai58dlaqjjsctigsepi5a@4ax.com...
Place the code in your Personal.xls and it will be available for all
workbooks.
Or download Chip Pearson's add-in.
LINK
Scroll down to Case Convert
Ahhh...thank you very much. Disregard my other question on this to your
other reply. I did not see this reply at the time. I'll try the download
as well.
I truly do appreciate your time and help. :-)
Jan :)
Gord Dibben MS Excel MVP
On Wed, 18 Jun 2008 17:45:59 -0400, "Jan :\)" <abuse@localhost.invalid
wrote:
Is there a way to create a Macro that I can use for all workbooks? I know
I
can copy them to another workbook, but, would like to be able to run the
Macros when needed in other workbooks as well.
Jan :)
"Jan " <abuse@localhost.invalid> wrote in message
news:OucTOfU0IHA.4572@TK2MSFTNGP03.phx.gbl...
Hi all, Excel 2007:
How can I change a range of text to Proper in place? Whenever I try to
change the text in a specific range, it will not change the text in that
range, it places it in some other area of the spreadsheet. I want to
change the text from Upper case to Proper case in B4:B15, but, no matter
where I place the cursor, or how I try to get the text to change in
those
cells, it does not does not change the text in those cells. If I select
the range and then select the PROPER in the Text list, it puts the
function in the first cell of the column, but, does not change the case
of
the text in those cells. So, what am I doing wrong? How would I do
this
for a range of several columns?
Jan
|
|
| |
| Page 1 of 2 .:. Goto page 1, 2 Next | |
|
|
