Change all from upper case to lower case???

J

jenniferlynnlee

Is there a third-party plug-in for changing text in all fields from all
caps to upper and lower (capital on all first letters) within Excel, or
is there a trick for achieving that conversion?

Thanks!
 
K

Ken Wright

Here's a range of macros from posts in this group. You want the lowercase one,
but the others may come in useful


Sub CAPS()
'select range and run this to change to all CAPS
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim Cel As Range
For Each Cel In Intersect(Selection, ActiveSheet.UsedRange)
Cel.Formula = UCase$(Cel.Formula)
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub MakeUpperCase()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = False Then
cell.Value = UCase(cell.Value)
End If
Next cell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub MakeLowercase()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = False Then
cell.Value = LCase(cell.Value)
End If
Next cell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub MakeProperCase()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim myCell As Range
Dim myRng As Range

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range that contains text--no formulas!"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = StrConv(myCell.Value, vbProperCase)
Next myCell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
B

Bob Phillips

In B1

=PROPER(A1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
K

Ken Wright

Oops - You want the ProperCase one, not the lowercase one - apologies

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :)
----------------------------------------------------------------------------



Ken Wright said:
Here's a range of macros from posts in this group. You want the lowercase one,
but the others may come in useful


Sub CAPS()
'select range and run this to change to all CAPS
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim Cel As Range
For Each Cel In Intersect(Selection, ActiveSheet.UsedRange)
Cel.Formula = UCase$(Cel.Formula)
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub MakeUpperCase()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = False Then
cell.Value = UCase(cell.Value)
End If
Next cell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub MakeLowercase()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = False Then
cell.Value = LCase(cell.Value)
End If
Next cell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Sub MakeProperCase()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim myCell As Range
Dim myRng As Range

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range that contains text--no formulas!"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = StrConv(myCell.Value, vbProperCase)
Next myCell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Click to expand...
 
J

jenniferlynnlee

These functions and macros seem to be exactly what I am looking for, bu
I failed to mention that I am a newbie to Excel. I've only launched i
twice, so after following the steps carefully in Help, I cannot get th
function to, well...function! (Actually, I did get it to convert on
cell but cannot for the life of me figure out what I did differently t
make it work!

I'm no doubt missing one stupid little step, but can someone walk m
through either using the function (and remember, I'm an Idiot t
Excel), or direct me in using the macros you posted?

I have a feeling that the Help section may be like a lot of manual
(which I NEVER use because of)...they sometimes leave out the mundan
but necessary for such as me.

Thanks so much!

(So close and yet so far...
 
K

Ken Wright

LOL - OK, here we go:-

The formulas you see such as =PROPER(A1), have to be entered in a different cell
to the one you are looking to convert, and they need to reference the cell you
do wish to convert, eg in this case the referenced cell with the data in is cell
A1, and the formula =PROPER(A1) would normally go into say cell B1 or C1 etc,
and then if say you have values in A1:A100 that need converting, you would copy
the cell B1 or C1 or whichever one held that formula, then select the area
B2:B100 or C2:C100 etc, and do Edit / Paste which will then paste the same
formula right the way down, but it will automatically adjust itself to reference
A1, A2, A3 etc

Having done that, you would then select all of the data in Col B or C, and then
copy it, select Col A again and Edit / paste Special / Values only to replace
the original data with the new cleansed data. Then just delete all those
formulas in Col B or C.

As for the macro:-

First off we want to give you a personal macro workbook, so hit Tools / Macro /
Macros / record new Macro, and thenfrom the dropdown choose to save it in your
personal macro workbook. Type a 1 in any cell, then hit the Stop recording
button that will have appeared on your screen somewhere.

Now hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you
need to search for
your personal macro workbook, and when you find it you may need to click on the
+ to expand it.
Within that you should see the following:-

VBAProject(Personal.xls)
Microsoft Excel Objects
Sheet1(Sheet1)
ThisWorkbook
Modules
Module1

Doubleclick on the Module1 bit and a big white space will open up. Delete the
stuff that's in there, which will likely be something like

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/05/2004 by Ken Wright
'

'
Range("C14").Select
ActiveCell.FormulaR1C1 = "1"
Range("C15").Select
End Sub


Then just copy and paste all those macros you were given (You can do it in one
go) into this space.

Then just hit File / Close and return to Microsoft Excel and save the file.

Now, whilst in any sheet, you should be able to simply do Tools / Macro / Macros
/ and then choose the relevant one from the list you will see. Choose the one
that says MakeProperCase and it should adjust all the data in your sheet without
the need for any formulas or copying and pasting.

If you get stuck, just post back.
 
D

David McRitchie

Hi Jennifer,
It might also be well to point out the difference between a Function
and a macro (besides how they are installed). See Chip Pearson's
Macros and Functions (differences)
http://www.cpearson.com/excel/differen.htm

If you do use the worksheet function you would need to copy it down with the
fill handle (unless you want to code each cell individually).
http://www.mvps.org/dmcritchie/excel/fillhand.htm

The macros that Ken originally included require you preselect your
selection area, so that the macro will work on a cell, a column(s),
a row(s) or any other rectangular area(s).

I'm sure you meant proper case, but your description also fits
sentence case where the first word of a sentence is capitalized
as opposed to Proper where the first letter of most words are
capitalized. The Proper Worksheet Function will capitalize
the first letter of each word, to alter that you must add additional
coding in your macro.

I usually would not bother with the PROPER Worksheet Function
as it requires a helper column and if you want to get rid of it, you
have to copy, edit, paste special, values and then you can
get rid of the original the formula is based on. With a macro
you select your area and run it -- much quicker, much cleaner.
Ken provided examples of both.

You might also want to take a look at my web page
Proper, and other Text changes -- Use of SpecialCells
PROPER (#proper)
http://www.mvps.org/dmcritchie/excel/proper.htm#proper
 
Top