ALL CAPS

webexcel

New Member
Joined
Aug 24, 2002
Messages
2
How can I select an entire colum and have all cells changed to "CAPS"? Some are already capitalized some cells are lower case, I need all cells in UPPER CASE.

Thanks,

WebExcel
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

davers5

Active Member
Joined
Feb 20, 2002
Messages
255
If the column that you want to capitalize is column A, the easiest way to do this is to add a column to the right of A. In the new column b, in cell b1 put the formula "=upper(a1)". Copy that down to the bottom of the column. Then select that entire column and paste the values (edit-pastespecial-values) onto column A. Delete column b.

Dave S.
This message was edited by davers5 on 2002-09-06 10:24
 

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
649
This is how I have done it:

(1) Insert a column beside the column that has the text. For this example, ColA is your column and ColB is the inserted column.
(2) In the first cell in ColB that corresponds to your text in ColA, enter the following formula: =Upper(A1).....assuming that you have text in A1 to convert to upper case.
(3) Copy the formula down the entire ColB. You should see all of your text from ColA now in upper case in ColB.
(4) Copy all of ColB and Paste Special in ColA--when you Paste Special be sure to choose VALUES.
(5) If your pasted ColA is correct, delete ColB.

HTH
 

nnadimi19

Board Regular
Joined
Jul 14, 2002
Messages
240
You need to download the ASAP utilities. In that you have a function that will do it for you without haveing to go through the trouble of creating new columns etc.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Just change the Range in the code below to your Range. This code go's in the Sheet Module of the sheet that is to be changed. JSW

Sub MakeUpper()
'Change the Range below to the working Range to use!
For Each c In Worksheets("Sheet1").Range("A3:AA3")
c.Value = UCase(c.Value)
Next c
End Sub


The code below will work automatically for any new text added to the workbook. It will not change current text like the code above!
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
Dim rng As Range
'Make all to be entered text Caps.
'Load from ThisWorkBook Module.
With Application
.EnableEvents = False
For Each rng In Source
rng.Value = UCase(rng.Value)
Next
.EnableEvents = True
End With
End Sub


You may change the code above to work on a range, if needed. JSW
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
This code when run will make the current selection Upper Case. JSW

Sub MakeSelectUC()
For Each c In ActiveCell.CurrentRegion.Cells
Selection.Value = UCase(c.Value)
Next c
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top