changing case in excel

  • Thread starter Thread starter andre
  • Start date Start date
A

andre

Is there a way in excel to convert words to upper case
and from upper case to lower case
 
andre

Have a look at the functions UPPER() and LOWER(), and you'll also get some
answers from these macro wizards on how to do it in situ using VBA!!

Andy.
 
Hi
using a helper column you may try the following in B1
=UPPER(A1)
or
=LOWER(A1)
and copy either down if column A stores your values. After this you may
copy column B and paste it with 'Edit - Paste Special' as 'Value'
 
Hi
and a macro solution (changes column A of the active sheet to upper
case

Sub change_upper()
Dim RowNdx As Long
Dim LastRow As Long
Dim wks_source As Worksheet
Set wks_source = ActiveSheet

LastRow = wks_source.Cells(Rows.count, "A").End(xlUp).row
For RowNdx = 1 To LastRow
With wks_source.Cells(RowNdx, "A")
If .Value <> "" Then
.Value = Upper(.Value)
'.Value = Lower(.Value) 'use this for lower case
End If
End With
Next RowNdx
End Sub
 
Hi Andre!

For the benefit of those picking up this in newusers group, here is
reply from worksheet.functions same question:

See this old post from Bill Manville

http://groups.google.com/groups?oi=djq&ic=1&selm=an_382221704

Perhaps you'll see the disadvantages associated with posting to more
than one group. It can cause wastage of time of those trying to help
and you have to go to two locations to see all the answers.

You might also look at:

Chip Pearson:
http://www.cpearson.com/excel/newposte.htm

David McRitchie:
http://www.mvps.org/dmcritchie/excel/posting.htm


But your always welcome as we're a tolerant bunch who don't have bad
hair days because it's all fallen out thanks to Excel.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Frank

"Upper" errors out.

Change to "UCase"

Also, if any formulas in the range, they get changed to values. May not be
what the OP desires.

Sub Upper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = UCase(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Gord Dibben Excel MVP
 
Hi Andre,
I have a page on the subject. I think choosing your
own selection is a more intuitive than limiting a macro
to Column A. Nor do I see the point in generating
a bunch of worksheet functions within a macro when the
macro can make the change and be done with it.
http://www.mvps.org/dmcritchie/excel/proper.htm
macros for making proper (title) case, upper case, or lower case
hopefully you are only capitalizing what has to be capitalized
like US Postal Service two letter zip state codes. I'd not
like to see my name and everything else in all capitals, it's
harder to read and takes more space within the columns..
 
Back
Top