How do i make text in one column all capital letters

[Guest]

i have one column of data i want to change the text into capital letters
without doing each item individually

 

[Bernie Deitrick]

TrainSpotter,

=UPPER(A1)

will convert the text in A1 to all caps. You can then copy the formula down
to match your data. Then you can copy / paste special values, and get rid
of your original text.

HTH,
Bernie
MS Excel MVP

 

[JulieD]

Hi

either use code

Sub changecase()
For Each cell In Range("B1:B100")
cell.Value = UCase(cell.Value)
Next
End Sub

change "B1:B100" for the actual range of cells that you want to do this to
- to use this code, right mouse click on the sheet tab with the data in it
and choose view / code
- you should see the name of your workbook on the left in bold, click on it
(if you can't see it, choose view / project explorer from the menu)
- choose insert / module
- on the right hand side where the white piece of paper has appeared, copy &
paste the code above (from Sub to End Sub)
make sure your cursor is somewhere in the code and press the F5 key to run
the code
- close the code window using the X in the top right
- before using code like this, please take a backup of your workbook.

or
insert a column to the right of the column you want to change
in the first cell type
=UPPER(A1)
where A1 is the first cell of the column to change
fill down (double click on little + on bottom right of the cell with the
formula in it)
then select this column, choose edit / copy
click on the column header of the lower case version of the data and choose
edit / paste special - values to replace the old data and then delete this
column

Hope this helps
Cheers
JulieD

 

[David McRitchie]

Assuming it is something you typed in so that you are looking at
constants and not formulas.

To fix existing entries use a macro see Upper_Case macro at
http://www.mvps.org/dmcritchie/excel/proper.htm#upper

To correct entries just after entering them use a Change Event Macro
like the one found just below the above subroutine.