Changing from lower case to upper case

[Hok Wong]

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.

 

[Bob I]

=UPPER(ref)

 

[Peo Sjoblom]

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

 

[Jim Thomlinson]

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

 

[Treadstone]

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

 

[Luke M]

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

 

[Gary''s Student]

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.

 

[Gord Dibben]

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

 

[Gord Dibben]

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

 

[jenn]

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

 

[Hok Wong]

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

 

[David McRitchie]

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.