How do i make text in one column all capital letters

Discussion in 'Microsoft Excel New Users' started by Guest, Sep 28, 2004.

  1. Guest

    Guest Guest

    i have one column of data i want to change the text into capital letters
    without doing each item individually
     
    Guest, Sep 28, 2004
    #1
    1. Advertisements

  2. 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

    "trainspotter20" <> wrote in message
    news:...
    > i have one column of data i want to change the text into capital letters
    > without doing each item individually
    >
     
    Bernie Deitrick, Sep 28, 2004
    #2
    1. Advertisements

  3. Guest

    JulieD Guest

    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


    "trainspotter20" <> wrote in message
    news:...
    >i have one column of data i want to change the text into capital letters
    > without doing each item individually
    >
     
    JulieD, Sep 28, 2004
    #3
  4. 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.

    ---
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

    "trainspotter20" <> wrote in message
    news:...
    > i have one column of data i want to change the text into capital letters
    > without doing each item individually
    >
     
    David McRitchie, Sep 28, 2004
    #4
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    2
    Views:
    178
    Guest
    Apr 10, 2006
  2. marrie
    Replies:
    9
    Views:
    212
    marrie
    Jul 26, 2006
  3. Robert11

    Making Everything Capital Letters In A Column: How ?

    Robert11, Oct 6, 2006, in forum: Microsoft Excel New Users
    Replies:
    8
    Views:
    425
    Guest
    Oct 9, 2006
  4. Guest
    Replies:
    7
    Views:
    288
    JoAnn Paules [MVP]
    Feb 15, 2007
  5. Mike Garcia

    Find a text from a column in a text string within another column?

    Mike Garcia, Oct 22, 2008, in forum: Microsoft Excel New Users
    Replies:
    1
    Views:
    283
    Roger Govier
    Oct 22, 2008
Loading...

Share This Page