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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|