PDA

View Full Version : Solved: Upper/Lower Case



GaryB
12-26-2006, 03:21 PM
Hi,

I have an excel file that I imported from one of our databases and everything in it is capitalized. I could export it as a comma delimited file into a word processing program to make it upper and lower case type , but I was wondering if there is any way, in Excel, to do this. As usual any help is always appreciated.

Thanks,

Gary

JimmyTheHand
12-26-2006, 04:00 PM
Hi Gary :hi:

Here's the code:
Sub test()
Dim c As Range
For Each c In ActiveSheet.Range(Cells(1, 1), Cells(1, 1).SpecialCells(xlLastCell)).Cells
If Left(c.Formula, 1) <> "=" Then c.Value = LCase(c.Value)
Next c
End Sub And here's what it does:
Takes all cells in the used range of ActiveSheet, and changes the cell values to lower case, except when there is a formula in the cell.

GaryB
12-26-2006, 04:21 PM
HI Jimmy,

I see what this does and it's great but I am looking for some that will leave the first letter of each word Capatalized os end up upper and lower case type.

thanks for the help

Gary

austenr
12-26-2006, 04:24 PM
if your data is in A1, place this in B1 and copy down.

=proper(a1)

JimmyTheHand
12-26-2006, 04:35 PM
In this case the code changes to
Dim c As Range
For Each c In ActiveSheet.Range(Cells(1, 1), Cells(1, 1).SpecialCells(xlLastCell)).Cells
If Left(c.Formula, 1) <> "=" Then c.Value = Application.WorksheetFunction.Proper(c.Value)
Next c
Thanks to austenr for the tip :thumb

Bob Phillips
12-27-2006, 04:19 AM
I don't understand why people test cell for formula as starting with = when there is a built-in method



If c.HasFormula Then ...

JimmyTheHand
12-27-2006, 04:40 AM
I don't understand why people test cell for formula as starting with = when there is a built-in method



If c.HasFormula Then ...

:wot
That's probably because people don't know about the built-in method...
I was ignorant, too, but not anymore :cool: I mean, not that ignorant.
Thanks for the tip :thumb

Bob Phillips
12-27-2006, 05:29 AM
That's probably because people don't know about the built-in method...
I was ignorant, too, but not anymore

Spreading the word ... :)

GaryB
12-27-2006, 07:54 AM
Thanks to everyone for the help. The code did what I needed it to do.

Thanks again,
Gary

Zack Barresse
12-27-2006, 08:15 AM
Also, the special cells last cell can be thrown off and is not really reliable. Either use the Cells.Find("*") or the Rows.Count method. Or another way to not loop through all cells is to use the special cells formulas. However you would need to error trap it as if there were no formulas it would error out that line of code. Should prove faster.

HTH