Making Everything Capital Letters In A Column: How ?

  • Thread starter Thread starter Robert11
  • Start date Start date
R

Robert11

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
 
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
 
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
 
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
 
Hi again,

Thanks so much.

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

Appreciate your time very much.

Regards,
Bob
--------------------
 
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
 
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. ;-)
 
Back
Top