How to change cases from small to capital in all cells?

Discussion in 'Microsoft Excel Setup' started by Guest, Sep 18, 2006.

  1. Guest

    Guest Guest

    I have to change the case of all characters from small to Capital in all
    cells of an excel file (Excel 2003 and lower).
    MS Word has a command called: Change case, which changes the case of
    selected letters from Capital to small and vise versa. Is there any
    equivalent of this command in Excel? I couldn’t find it. If there is not,
    what is the fastest way to change all letters from small to Capital in Excel?
    If there is any Macro or VBA solution, please let me know.
    One way might be to Copy/paste to word, do the change and return.


    --
    Rasoul Khoshravan Azar
    Kobe University, Kobe, Japan
     
    Guest, Sep 18, 2006
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    Try this macro:
    Sub chtocaps()
    For Each cell In ActiveSheet.UsedRange
    cell.Value = UCase(cell.Value)
    Next cell
    End Sub

    Regards,
    Stefi


    „Khoshravan†ezt írta:

    > I have to change the case of all characters from small to Capital in all
    > cells of an excel file (Excel 2003 and lower).
    > MS Word has a command called: Change case, which changes the case of
    > selected letters from Capital to small and vise versa. Is there any
    > equivalent of this command in Excel? I couldn’t find it. If there is not,
    > what is the fastest way to change all letters from small to Capital in Excel?
    > If there is any Macro or VBA solution, please let me know.
    > One way might be to Copy/paste to word, do the change and return.
    >
    >
    > --
    > Rasoul Khoshravan Azar
    > Kobe University, Kobe, Japan
     
    Guest, Sep 18, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    Thanks for your solution. It helped me a lot. As its extension:
    Is it possible to extend this Macro to do the same job for different files
    (let say 14 files) inside one folder? All files have only one sheet.
    If it is not possible, I can condensate all files into single file with 14
    sheets. Then would it be possible to amend that Macro to perform the job for
    14 sheets inside single file?
    Thanks in Advance

    --
    Rasoul Khoshravan Azar
    Kobe University, Kobe, Japan


    "Stefi" wrote:

    > Try this macro:
    > Sub chtocaps()
    > For Each cell In ActiveSheet.UsedRange
    > cell.Value = UCase(cell.Value)
    > Next cell
    > End Sub
    >
    > Regards,
    > Stefi
    >
    >
    > „Khoshravan†ezt írta:
    >
    > > I have to change the case of all characters from small to Capital in all
    > > cells of an excel file (Excel 2003 and lower).
    > > MS Word has a command called: Change case, which changes the case of
    > > selected letters from Capital to small and vise versa. Is there any
    > > equivalent of this command in Excel? I couldn’t find it. If there is not,
    > > what is the fastest way to change all letters from small to Capital in Excel?
    > > If there is any Macro or VBA solution, please let me know.
    > > One way might be to Copy/paste to word, do the change and return.
    > >
    > >
    > > --
    > > Rasoul Khoshravan Azar
    > > Kobe University, Kobe, Japan
     
    Guest, Sep 18, 2006
    #3
  4. Guest

    Gord Dibben Guest

    And be prepared to have all your formulas change to values.

    Use this modified version which will not affect formulas.

    Sub chtocaps()
    For Each cell In ActiveSheet.UsedRange
    cell.Formula = UCase(cell.Formula)
    Next cell
    End Sub


    Gord Dibben MS Excel MVP

    On Mon, 18 Sep 2006 01:17:01 -0700, Stefi <>
    wrote:

    >Try this macro:
    >Sub chtocaps()
    >For Each cell In ActiveSheet.UsedRange
    > cell.Value = UCase(cell.Value)
    >Next cell
    >End Sub
    >
    >Regards,
    >Stefi
    >
    >
    >„Khoshravan” ezt írta:
    >
    >> I have to change the case of all characters from small to Capital in all
    >> cells of an excel file (Excel 2003 and lower).
    >> MS Word has a command called: Change case, which changes the case of
    >> selected letters from Capital to small and vise versa. Is there any
    >> equivalent of this command in Excel? I couldn’t find it. If there is not,
    >> what is the fastest way to change all letters from small to Capital in Excel?
    >> If there is any Macro or VBA solution, please let me know.
    >> One way might be to Copy/paste to word, do the change and return.
    >>
    >>
    >> --
    >> Rasoul Khoshravan Azar
    >> Kobe University, Kobe, Japan
     
    Gord Dibben, Sep 18, 2006
    #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. L Schmandt

    how to default all cells to wrap text?

    L Schmandt, Jun 17, 2004, in forum: Microsoft Excel Setup
    Replies:
    1
    Views:
    442
    Gord Dibben
    Jun 17, 2004
  2. Guest
    Replies:
    0
    Views:
    411
    Guest
    Jan 26, 2005
  3. Guest
    Replies:
    1
    Views:
    232
    Dave Peterson
    Jan 27, 2006
  4. Zack
    Replies:
    1
    Views:
    232
  5. Dan

    excel changes the word true to capital letter

    Dan, Jun 13, 2008, in forum: Microsoft Excel Setup
    Replies:
    1
    Views:
    270
    Niek Otten
    Jun 13, 2008
Loading...

Share This Page