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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Khoshravan - 18 Sep 2006 08:33 GMT

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.

Signature

Rasoul Khoshravan Azar
Kobe University, Kobe, Japan

Reply to this Message

Stefi - 18 Sep 2006 09:17 GMT

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).
[quoted text clipped - 4 lines]
> 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.

Reply to this Message

Khoshravan - 18 Sep 2006 21:02 GMT

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

Signature

Rasoul Khoshravan Azar
Kobe University, Kobe, Japan

> Try this macro:
> Sub chtocaps()
[quoted text clipped - 16 lines]
> > 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.

Reply to this Message

Gord Dibben - 18 Sep 2006 21:26 GMT

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

>Try this macro:
>Sub chtocaps()
[quoted text clipped - 16 lines]
>> 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.

Reply to this Message