A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Making Everything Capital Letters In A Column: How ?



 
 
Thread Tools Display Modes
  #1  
Old October 6th, 2006, 11:38 AM posted to microsoft.public.excel.newusers
Robert11
external usenet poster
 
Posts: 37
Default Making Everything Capital Letters In A Column: How ?

Hello:

I have a long column with various text in the cells.
Both capital letters, and lower case. Some cells have both.

Would like to make everything CAPS in all the cells, hopefully in one shot.

How can I do this, please ?

Thanks,
Bob


  #2  
Old October 6th, 2006, 11:47 AM posted to microsoft.public.excel.newusers
JE McGimpsey
external usenet poster
 
Posts: 2,468
Default Making Everything Capital Letters In A Column: How ?

See

http://www.mvps.org/dmcritchie/excel/proper.htm#upper




In article ,
"Robert11" wrote:

Hello:

I have a long column with various text in the cells.
Both capital letters, and lower case. Some cells have both.

Would like to make everything CAPS in all the cells, hopefully in one shot.

How can I do this, please ?

Thanks,
Bob

  #3  
Old October 6th, 2006, 11:58 AM posted to microsoft.public.excel.newusers
Paul B
external usenet poster
 
Posts: 459
Default Making Everything Capital Letters In A Column: How ?

Bob, you could use a helper column and put in =UPPER(A1) and fill down as
far as needed, then copy and paste special valves back over your data, or
you could use a macro, select the cells you want to change and run this

Sub Change_To_Upper_Case()
' change selected text to UPPER CASE
Application.ScreenUpdating = False
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = UCase(Rng.Value)
End If
Next Rng
Application.ScreenUpdating = True
End Sub

To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is at the top-left of the spreadsheet this will
open the VBA editor, in Project Explorer click on your workbook name, if you
don't see it press CTRL + r to open the Project Explorer, then go to insert,
module, and paste the code in the window that opens on the right hand side,
press Alt and Q to close this window and go back to your workbook and press
alt and F8, this will bring up a box to pick the Macro from, click on the
Macro name to run it. If you are using excel 2000 or newer you may have to
change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it to
medium

And if you are new to macros you may also what to have a look here on
getting started with macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
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 2002 & 2003

"Robert11" wrote in message
...
Hello:

I have a long column with various text in the cells.
Both capital letters, and lower case. Some cells have both.

Would like to make everything CAPS in all the cells, hopefully in one

shot.

How can I do this, please ?

Thanks,
Bob




  #4  
Old October 6th, 2006, 01:45 PM posted to microsoft.public.excel.newusers
Robert11
external usenet poster
 
Posts: 37
Default To Paul B. Making Everything Capital Letters In A Column: How ?

Hi,

Thanks so much for help.
Your explanation is very clear, and I hate to appear dumber than I really
am, but am having trouble implementying it. Will blame it on old age.

Could you please give me really specific step by step instructions, for
e.g., converting this
text to all Caps. Have tried several things, but all I seem able to do is
get
the cell in A1 converted; never the rest. Assume I'm a total idiot with
this stuff.

Lets say that I would like it all to appear, converted, in Col B:

A B

1 wwcc
2 vghMM
3 eerTY
4 youp

What do I set up as dummy cells or columns ?
What do I select ?
Then (exactly) what ?

Thanks again,
Really appreciate it.

Bob
----------------------------



"Paul B" wrote in message
...
Bob, you could use a helper column and put in =UPPER(A1) and fill down as
far as needed, then copy and paste special valves back over your data, or
you could use a macro, select the cells you want to change and run this

Sub Change_To_Upper_Case()
' change selected text to UPPER CASE
Application.ScreenUpdating = False
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = UCase(Rng.Value)
End If
Next Rng
Application.ScreenUpdating = True
End Sub

To put in this macro, from your workbook right-click the workbook's icon
and
pick View Code. This icon is at the top-left of the spreadsheet this will
open the VBA editor, in Project Explorer click on your workbook name, if
you
don't see it press CTRL + r to open the Project Explorer, then go to
insert,
module, and paste the code in the window that opens on the right hand
side,
press Alt and Q to close this window and go back to your workbook and
press
alt and F8, this will bring up a box to pick the Macro from, click on the
Macro name to run it. If you are using excel 2000 or newer you may have
to
change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it
to
medium

And if you are new to macros you may also what to have a look here on
getting started with macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
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 2002 & 2003

"Robert11" wrote in message
...
Hello:

I have a long column with various text in the cells.
Both capital letters, and lower case. Some cells have both.

Would like to make everything CAPS in all the cells, hopefully in one

shot.

How can I do this, please ?

Thanks,
Bob






  #5  
Old October 6th, 2006, 02:06 PM posted to microsoft.public.excel.newusers
Paul B
external usenet poster
 
Posts: 459
Default To Paul B. Making Everything Capital Letters In A Column: How ?

Robert, if you want to use the upper function, put =UPPER(A1) in B1, enter,
select cell B1 there will be a black square in the lower right hand corner,
you can double click on it and it will fill column B down with the formula
until it finds a blank row in column A, or you can put your mouse on the
square, your cursor will change to a plus sign, left click and drag down to
the end of your data, you should then have a upper case in column B. If you
want to replace your original data with the upper case from column B, select
all the data in column B, copy the data select A1 edit, paste special,
valves, this should put the data from column B into column A, you can then
delete the data from column B.

If you want to use the macro, put it in as described, select all the data in
column A and then run the macro as described, this should change all the
selected data to caps.

If you need anymore help post back
Paul

"Robert11" wrote in message
. ..
Hi,

Thanks so much for help.
Your explanation is very clear, and I hate to appear dumber than I really
am, but am having trouble implementying it. Will blame it on old age.

Could you please give me really specific step by step instructions, for
e.g., converting this
text to all Caps. Have tried several things, but all I seem able to do is
get
the cell in A1 converted; never the rest. Assume I'm a total idiot with
this stuff.

Lets say that I would like it all to appear, converted, in Col B:

A B

1 wwcc
2 vghMM
3 eerTY
4 youp

What do I set up as dummy cells or columns ?
What do I select ?
Then (exactly) what ?

Thanks again,
Really appreciate it.

Bob
----------------------------



"Paul B" wrote in message
...
Bob, you could use a helper column and put in =UPPER(A1) and fill down as
far as needed, then copy and paste special valves back over your data, or
you could use a macro, select the cells you want to change and run this

Sub Change_To_Upper_Case()
' change selected text to UPPER CASE
Application.ScreenUpdating = False
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = UCase(Rng.Value)
End If
Next Rng
Application.ScreenUpdating = True
End Sub

To put in this macro, from your workbook right-click the workbook's icon
and
pick View Code. This icon is at the top-left of the spreadsheet this will
open the VBA editor, in Project Explorer click on your workbook name, if
you
don't see it press CTRL + r to open the Project Explorer, then go to
insert,
module, and paste the code in the window that opens on the right hand
side,
press Alt and Q to close this window and go back to your workbook and
press
alt and F8, this will bring up a box to pick the Macro from, click on the
Macro name to run it. If you are using excel 2000 or newer you may have
to
change the macro security settings to get the macro to run. To change the
security settings go to tools, macro, security, security level and set it
to
medium

And if you are new to macros you may also what to have a look here on
getting started with macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
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 2002 & 2003

"Robert11" wrote in message
...
Hello:

I have a long column with various text in the cells.
Both capital letters, and lower case. Some cells have both.

Would like to make everything CAPS in all the cells, hopefully in one

shot.

How can I do this, please ?

Thanks,
Bob








  #6  
Old October 6th, 2006, 06:09 PM posted to microsoft.public.excel.newusers
Robert11
external usenet poster
 
Posts: 37
Default To Paul B. Again: Making Everything Capital Letters In A Column: How ?

Hi again,

Thanks so much.

You have a great, clear, way of explaining things.

Appreciate your time very much.

Regards,
Bob
--------------------

"Paul B" wrote in message
...
Robert, if you want to use the upper function, put =UPPER(A1) in B1,
enter, select cell B1 there will be a black square in the lower right hand
corner, you can double click on it and it will fill column B down with the
formula until it finds a blank row in column A, or you can put your mouse
on the square, your cursor will change to a plus sign, left click and drag
down to the end of your data, you should then have a upper case in column
B. If you want to replace your original data with the upper case from
column B, select all the data in column B, copy the data select A1 edit,
paste special, valves, this should put the data from column B into column
A, you can then delete the data from column B.

If you want to use the macro, put it in as described, select all the data
in column A and then run the macro as described, this should change all
the selected data to caps.

If you need anymore help post back
Paul

"Robert11" wrote in message
. ..
Hi,

Thanks so much for help.
Your explanation is very clear, and I hate to appear dumber than I really
am, but am having trouble implementying it. Will blame it on old age.

Could you please give me really specific step by step instructions, for
e.g., converting this
text to all Caps. Have tried several things, but all I seem able to do
is get
the cell in A1 converted; never the rest. Assume I'm a total idiot with
this stuff.

Lets say that I would like it all to appear, converted, in Col B:

A B

1 wwcc
2 vghMM
3 eerTY
4 youp

What do I set up as dummy cells or columns ?
What do I select ?
Then (exactly) what ?

Thanks again,
Really appreciate it.

Bob
----------------------------



"Paul B" wrote in message
...
Bob, you could use a helper column and put in =UPPER(A1) and fill down
as
far as needed, then copy and paste special valves back over your data,
or
you could use a macro, select the cells you want to change and run this

Sub Change_To_Upper_Case()
' change selected text to UPPER CASE
Application.ScreenUpdating = False
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = UCase(Rng.Value)
End If
Next Rng
Application.ScreenUpdating = True
End Sub

To put in this macro, from your workbook right-click the workbook's icon
and
pick View Code. This icon is at the top-left of the spreadsheet this
will
open the VBA editor, in Project Explorer click on your workbook name, if
you
don't see it press CTRL + r to open the Project Explorer, then go to
insert,
module, and paste the code in the window that opens on the right hand
side,
press Alt and Q to close this window and go back to your workbook and
press
alt and F8, this will bring up a box to pick the Macro from, click on
the
Macro name to run it. If you are using excel 2000 or newer you may
have to
change the macro security settings to get the macro to run. To change
the
security settings go to tools, macro, security, security level and set
it to
medium

And if you are new to macros you may also what to have a look here on
getting started with macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
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 2002 & 2003

"Robert11" wrote in message
...
Hello:

I have a long column with various text in the cells.
Both capital letters, and lower case. Some cells have both.

Would like to make everything CAPS in all the cells, hopefully in one
shot.

How can I do this, please ?

Thanks,
Bob










  #7  
Old October 6th, 2006, 06:28 PM posted to microsoft.public.excel.newusers
Paul B
external usenet poster
 
Posts: 459
Default To Paul B. Again: Making Everything Capital Letters In A Column: How ?

Your welcome, thanks for the feedback
--
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 2002 & 2003

"Robert11" wrote in message
. ..
Hi again,

Thanks so much.

You have a great, clear, way of explaining things.

Appreciate your time very much.

Regards,
Bob
--------------------

"Paul B" wrote in message
...
Robert, if you want to use the upper function, put =UPPER(A1) in B1,
enter, select cell B1 there will be a black square in the lower right
hand corner, you can double click on it and it will fill column B down
with the formula until it finds a blank row in column A, or you can put
your mouse on the square, your cursor will change to a plus sign, left
click and drag down to the end of your data, you should then have a upper
case in column B. If you want to replace your original data with the
upper case from column B, select all the data in column B, copy the data
select A1 edit, paste special, valves, this should put the data from
column B into column A, you can then delete the data from column B.

If you want to use the macro, put it in as described, select all the data
in column A and then run the macro as described, this should change all
the selected data to caps.

If you need anymore help post back
Paul

"Robert11" wrote in message
. ..
Hi,

Thanks so much for help.
Your explanation is very clear, and I hate to appear dumber than I
really am, but am having trouble implementying it. Will blame it on old
age.

Could you please give me really specific step by step instructions, for
e.g., converting this
text to all Caps. Have tried several things, but all I seem able to do
is get
the cell in A1 converted; never the rest. Assume I'm a total idiot with
this stuff.

Lets say that I would like it all to appear, converted, in Col B:

A B

1 wwcc
2 vghMM
3 eerTY
4 youp

What do I set up as dummy cells or columns ?
What do I select ?
Then (exactly) what ?

Thanks again,
Really appreciate it.

Bob
----------------------------



"Paul B" wrote in message
...
Bob, you could use a helper column and put in =UPPER(A1) and fill down
as
far as needed, then copy and paste special valves back over your data,
or
you could use a macro, select the cells you want to change and run this

Sub Change_To_Upper_Case()
' change selected text to UPPER CASE
Application.ScreenUpdating = False
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = UCase(Rng.Value)
End If
Next Rng
Application.ScreenUpdating = True
End Sub

To put in this macro, from your workbook right-click the workbook's
icon and
pick View Code. This icon is at the top-left of the spreadsheet this
will
open the VBA editor, in Project Explorer click on your workbook name,
if you
don't see it press CTRL + r to open the Project Explorer, then go to
insert,
module, and paste the code in the window that opens on the right hand
side,
press Alt and Q to close this window and go back to your workbook and
press
alt and F8, this will bring up a box to pick the Macro from, click on
the
Macro name to run it. If you are using excel 2000 or newer you may
have to
change the macro security settings to get the macro to run. To change
the
security settings go to tools, macro, security, security level and set
it to
medium

And if you are new to macros you may also what to have a look here on
getting started with macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
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 2002 & 2003

"Robert11" wrote in message
...
Hello:

I have a long column with various text in the cells.
Both capital letters, and lower case. Some cells have both.

Would like to make everything CAPS in all the cells, hopefully in one
shot.

How can I do this, please ?

Thanks,
Bob












  #8  
Old October 7th, 2006, 01:34 AM posted to microsoft.public.excel.newusers
JoAnn Paules [MVP]
external usenet poster
 
Posts: 5,489
Default Making Everything Capital Letters In A Column: How ?

I know a way to do it but it's pretty much low-tech. I would copy the column
and paste it into Word. Edit - Select all - shift+F3 until I get all caps,
copy, and paste it back into Excel.

Sure.....go ahead and laugh at me and my silly ways. But it works. ;-)

--

JoAnn Paules
MVP Microsoft [Publisher]




"Robert11" wrote in message
...
Hello:

I have a long column with various text in the cells.
Both capital letters, and lower case. Some cells have both.

Would like to make everything CAPS in all the cells, hopefully in one
shot.

How can I do this, please ?

Thanks,
Bob



  #9  
Old October 9th, 2006, 02:46 AM posted to microsoft.public.excel.newusers
Teethless mama
external usenet poster
 
Posts: 3,722
Default Making Everything Capital Letters In A Column: How ?

=UPPER(A1)

Copy as far as needed.


"Robert11" wrote:

Hello:

I have a long column with various text in the cells.
Both capital letters, and lower case. Some cells have both.

Would like to make everything CAPS in all the cells, hopefully in one shot.

How can I do this, please ?

Thanks,
Bob



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:30 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.