Leading capital

[Lennart Back]

Hi,
I work in Excel 7.0 and wonder:
Is it possible to create a macro that changes entries in several cells like
this:
from AAAAA to Aaaaa or aaaaa
from BDHF to Bdhf or bdhf
from LENNART to Lennart or lennart
Lennart

[Harald Staff]

Hi Lennart

Sure thing

Sub Caps()
Range(Cells(1, 1), Selection.SpecialCells(xlLastCell)).Select
For Each cell In Selection
cell.Value = UCase(Left(cell.Value, 1)) & LCase(Mid(cell.Value, 2))
Next cell
End Sub

Sub Lowcaps()
Range(Cells(1, 1), Selection.SpecialCells(xlLastCell)).Select
For Each cell In Selection
cell.Value = LCase(cell.Value)
Next cell
End Sub

Best wishes Harald

[DMcRitchie]

Hi Lennart,
Harold's answer fits your description and title . It capitalizes only the
first letter of the first word in a cell. If you meant on the other hand to
make all words proper (proper nouns) as in names and addresses there is PROPER
which is both a worksheet function and VBA.

Help --> Index --> PROPER Worksheet Function

VBA Example:
Sub Proper_Case()
For Each Cell In Selection
Cell = Application.Proper(Cell)
Next
End Sub

see http://members.aol.com/rexx03/excel/proper.htm

also Re: Change Case, a reply posting by Bill Manville.  Bill's code will also
handle cells with formulas by enclosing the formula rather than simply changing
data.

http://www.dejanews.com/getdoc.xp?AN=382221704
Not the above refers to a specific article in DejaNews.

HTH,
David McRitchie
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm

[Jake]

You can also use Application.Proper to capitalize the first letter of
each word in a cell.

[DMcRi...@aol.com]

Hi Lennart,
My mistake on posting (Aug 20)-- I left out value as in Cell.Value =
The other two macros on my web page included this.

It is interesting that either of the following will work, I have a lot to
learn on VB objects. Well actually I guess there was nothing to say what
cell actually was so it was probably just an integer and nothing to do with
the spreadsheet.

Sub Proper_Case()

For Each cell In Selection

cell.value = Application.Proper(cell)
Next
End Sub

--or the addition of Dim statement--

Sub Proper_Case()
Dim cell As Range

For Each cell In Selection

cell = Application.Proper(cell)
Next
End Sub

Lennart, Thanks for pointing this out in your email question. The code I
actually use wasn't what I copied from my web page <embarrassment>. I have
corrected my web page.

David McRitchie
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm

Corrected Page: http://members.aol.com/rexx03/excel/proper.htm

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

[Nick Manton]

Hi Lennart,

UCase(cell)
LCase(cell)
Application.Proper(cell)

Or, if you want a worksheet solution:

=UPPER(cell)
=LOWER(cell)
=PROPER(cell)

HTH,

Nick

--
Nick Manton
Chemistry & Chemical Engineering Labs.
SRI International, Menlo Park, CA
ni...@sri.com

Lennart Back wrote in message
<26WC1.2783$S45.4...@nntpserver.swip.net>...