How do i make text in one column all capital letters

T

trainspotter20

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

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top