If I have an excel document where most of the text within the cells have been
written in lowercase, is there an easy way to change all the text into
uppercase? I thought i'd seen an option for that years ago, but typically,
now i want to use it, i can't find it.

=UPPER(ref)

Hok Wong wrote:

If I have an excel document where most of the text within the cells have been
written in lowercase, is there an easy way to change all the text into
uppercase? I thought i'd seen an option for that years ago, but typically,
now i want to use it, i can't find it.

Here is what I use, first read this link on how to install the macros then
just select the range in question and run them, Save them in your PERSONAL
macro workbook which is hidden by default. The below has 3 macros, upper,
lower and proper case

You can also use a help column and a formula

=UPPER(A1)

=LOWER(A1)

=PROPER(A1)

but a macro makes sense especially if you need to do it more than once






http://www.mvps.org/dmcritchie/excel/install.htm




Option Explicit
Sub Upper_Case()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
On Error Resume Next
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Cell.Formula = UCase(Cell.Formula)
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Sub PropCase()
Application.DisplayAlerts = False
Dim R As Range
For Each R In Selection.Cells
If R.HasFormula Then
R.Formula = "=PROPER(" & Mid(R.Formula, 2) & ")"
Else
R.Value = Application.Proper(R.Value)
End If
Next
Application.DisplayAlerts = True
End Sub
Sub Lower_Case()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
On Error Resume Next
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Cell.Formula = LCase(Cell.Formula)
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

--


Regards,


Peo Sjoblom


"Hok Wong" wrote in message
...
If I have an excel document where most of the text within the cells have
been
written in lowercase, is there an easy way to change all the text into
uppercase? I thought i'd seen an option for that years ago, but
typically,
now i want to use it, i can't find it.

There is an Upper function

=Upper(A1)

Will take the contents of A1 and change it too upper case. You can then
paste it back special values only if that is necessary...
--
HTH...

Jim Thomlinson


"Hok Wong" wrote:

If I have an excel document where most of the text within the cells have been
written in lowercase, is there an easy way to change all the text into
uppercase? I thought i'd seen an option for that years ago, but typically,
now i want to use it, i can't find it.

=UPPER(text)
Where (text) is the cell you want to change

"Hok Wong" wrote:

If I have an excel document where most of the text within the cells have been
written in lowercase, is there an easy way to change all the text into
uppercase? I thought i'd seen an option for that years ago, but typically,
now i want to use it, i can't find it.

using formulas, you can use the UPPER function.

via macros, you can use the UCase operator. Something like:

Sub CapMe()
For Each ws In ThisWorkbook.Worksheets
'Adjust as desired
For Each cell In ws.Range("A1:Z200")
cell.Value = UCase(cell.Value)
Next cell
Next ws
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hok Wong" wrote:

If I have an excel document where most of the text within the cells have been
written in lowercase, is there an easy way to change all the text into
uppercase? I thought i'd seen an option for that years ago, but typically,
now i want to use it, i can't find it.

With text in A1:

=UPPER(A1) will display the text in all upper case

If you want to convert the text "in place", then try this simple macro:

Sub GoToUpper()
For Each r In Selection
r.Value = UCase(r.Value)
Next
End Sub

First select the cells and then run the macro.
--
Gary''s Student - gsnu200908


"Hok Wong" wrote:

If I have an excel document where most of the text within the cells have been
written in lowercase, is there an easy way to change all the text into
uppercase? I thought i'd seen an option for that years ago, but typically,
now i want to use it, i can't find it.

Just make sure there are no formulas in the cells you select.

This code will change all to values only.


Gord Dibben MS Excel MVP

On Wed, 4 Nov 2009 10:25:28 -0800, Gary''s Student
wrote:

With text in A1:

=UPPER(A1) will display the text in all upper case

If you want to convert the text "in place", then try this simple macro:

Sub GoToUpper()
For Each r In Selection
r.Value = UCase(r.Value)
Next
End Sub

First select the cells and then run the macro.

Just make sure there are no formulas in the cells you select.

This code will change all to values only.


Gord Dibben MS Excel MVP

On Wed, 4 Nov 2009 10:25:13 -0800, Luke M
wrote:

using formulas, you can use the UPPER function.

via macros, you can use the UCase operator. Something like:

Sub CapMe()
For Each ws In ThisWorkbook.Worksheets
'Adjust as desired
For Each cell In ws.Range("A1:Z200")
cell.Value = UCase(cell.Value)
Next cell
Next ws
End Sub

You also have the option of =proper( )
rather than upper. if you need

"Hok Wong" wrote:

If I have an excel document where most of the text within the cells have been
written in lowercase, is there an easy way to change all the text into
uppercase? I thought i'd seen an option for that years ago, but typically,
now i want to use it, i can't find it.

Sorry for late reply, but thanks to everyone who responded. was very helpful
Thanks

"Hok Wong" wrote:

If I have an excel document where most of the text within the cells have been
written in lowercase, is there an easy way to change all the text into
uppercase? I thought i'd seen an option for that years ago, but typically,
now i want to use it, i can't find it.

If you want a macro solution that only processes text cells look for (#upper) in

Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm#upper

you could modify it to change all cells including those with formulas
because the macro is actually converting formula of text cells,
including formulas would still be okay.

--
HTH,
David McRitchie
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm



"Hok Wong" wrote in message ...
Sorry for late reply, but thanks to everyone who responded. was very helpful
Thanks

"Hok Wong" wrote:

If I have an excel document where most of the text within the cells have been
written in lowercase, is there an easy way to change all the text into
uppercase? I thought i'd seen an option for that years ago, but typically,
now i want to use it, i can't find it.