I have a spread sheet created with Excel 2007. One colum has first and last
names in all caps. How can I convert the first letter for each name to be
caps and the rest of the letters to be lowercase? I want to do the whole
colum at once.

Thanks

--
----------------------------------------------------
This mailbox protected from unsolicited email by Spam Alarm
from Dignity Software http://www.dignitysoftware.com

One way
Sub makeallproper()
mc = "c"
lr = Cells(Rows.Count, mc).End(xlUp).Row
Range(Cells(1, mc), Cells(lr, mc)).Value = _
Application.Proper(Range(Cells(1, mc), Cells(lr, mc)).Value)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Doug" <doug57@www.com> wrote in message
news:uY8jneerIHA.1316@TK2MSFTNGP06.phx.gbl...
>I have a spread sheet created with Excel 2007. One colum has first and last
>names in all caps. How can I convert the first letter for each name to be
>caps and the rest of the letters to be lowercase? I want to do the whole
>colum at once.
>
> Thanks
>
> --
> ----------------------------------------------------
> This mailbox protected from unsolicited email by Spam Alarm
> from Dignity Software http://www.dignitysoftware.com
>

The worksheet function "Proper" capitalizes the first letter of each string
and makes the rest lower case. In your example, I am guessing that it
should work fine, though you need to check it closely. Sometimes the last
name has more than one part, e.g. in Ludwig van Beethoven has the "van" all
in lower case. There are likely other cases, perhaps Asian surnames (though
none come to mind) where it will give you an incorrect result; i.e. just
what you asked for but not what you need. So use it judiciously.



"Doug" <doug57@www.com> wrote in message
news:uY8jneerIHA.1316@TK2MSFTNGP06.phx.gbl...
>I have a spread sheet created with Excel 2007. One colum has first and last
>names in all caps. How can I convert the first letter for each name to be
>caps and the rest of the letters to be lowercase? I want to do the whole
>colum at once.
>
> Thanks
>
> --
> ----------------------------------------------------
> This mailbox protected from unsolicited email by Spam Alarm
> from Dignity Software http://www.dignitysoftware.com
>

Is this right?
=proper(B4:B40)

--
----------------------------------------------------
This mailbox protected from unsolicited email by Spam Alarm
from Dignity Software http://www.dignitysoftware.com
"The poster formerly known as Colleyville Alan" <nospam@nospam.net> wrote in
message news:481dc136$0$5167$4c368faf@roadrunner.com...
> The worksheet function "Proper" capitalizes the first letter of each
> string and makes the rest lower case. In your example, I am guessing that
> it should work fine, though you need to check it closely. Sometimes the
> last name has more than one part, e.g. in Ludwig van Beethoven has the
> "van" all in lower case. There are likely other cases, perhaps Asian
> surnames (though none come to mind) where it will give you an incorrect
> result; i.e. just what you asked for but not what you need. So use it
> judiciously.
>
>
>
> "Doug" <doug57@www.com> wrote in message
> news:uY8jneerIHA.1316@TK2MSFTNGP06.phx.gbl...
>>I have a spread sheet created with Excel 2007. One colum has first and
>>last names in all caps. How can I convert the first letter for each name
>>to be caps and the rest of the letters to be lowercase? I want to do the
>>whole colum at once.
>>
>> Thanks
>>
>> --
>> ----------------------------------------------------
>> This mailbox protected from unsolicited email by Spam Alarm
>> from Dignity Software http://www.dignitysoftware.com
>>
>

no
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Doug" <doug57@www.com> wrote in message
news:usRbZRfrIHA.5580@TK2MSFTNGP04.phx.gbl...
> Is this right?
> =proper(B4:B40)
>
> --
> ----------------------------------------------------
> This mailbox protected from unsolicited email by Spam Alarm
> from Dignity Software http://www.dignitysoftware.com
> "The poster formerly known as Colleyville Alan" <nospam@nospam.net> wrote
> in message news:481dc136$0$5167$4c368faf@roadrunner.com...
>> The worksheet function "Proper" capitalizes the first letter of each
>> string and makes the rest lower case. In your example, I am guessing
>> that it should work fine, though you need to check it closely. Sometimes
>> the last name has more than one part, e.g. in Ludwig van Beethoven has
>> the "van" all in lower case. There are likely other cases, perhaps Asian
>> surnames (though none come to mind) where it will give you an incorrect
>> result; i.e. just what you asked for but not what you need. So use it
>> judiciously.
>>
>>
>>
>> "Doug" <doug57@www.com> wrote in message
>> news:uY8jneerIHA.1316@TK2MSFTNGP06.phx.gbl...
>>>I have a spread sheet created with Excel 2007. One colum has first and
>>>last names in all caps. How can I convert the first letter for each name
>>>to be caps and the rest of the letters to be lowercase? I want to do the
>>>whole colum at once.
>>>
>>> Thanks
>>>
>>> --
>>> ----------------------------------------------------
>>> This mailbox protected from unsolicited email by Spam Alarm
>>> from Dignity Software http://www.dignitysoftware.com
>>>
>>
>
>

No. You might have in, say, cell C4 =proper(b4) then in cell C5 =proper(b5),
etc; i.e. you copy the formula to a parallel column with formulas matching
each cell.

Depending upon the application, you might want to use the VBA approach that
Don Guillett showed you, but if you simply want to quickly convert the
information using a formula in a worksheet would prove faster.

If you then want to change the results from formulas to text, perhaps to
email someone or for use in another application, you could highlight the
cells with the formulas, and then choose from the menu:
edit | copy |
followed by
edit | pastespecial | values
I do this so often that I hardly can remember the menu commands, I just
remember alt-e,c then alt-e,s,v



"Doug" <doug57@www.com> wrote in message
news:usRbZRfrIHA.5580@TK2MSFTNGP04.phx.gbl...
> Is this right?
> =proper(B4:B40)
>
> --
> ----------------------------------------------------
> This mailbox protected from unsolicited email by Spam Alarm
> from Dignity Software http://www.dignitysoftware.com
> "The poster formerly known as Colleyville Alan" <nospam@nospam.net> wrote
> in message news:481dc136$0$5167$4c368faf@roadrunner.com...
>> The worksheet function "Proper" capitalizes the first letter of each
>> string and makes the rest lower case. In your example, I am guessing
>> that it should work fine, though you need to check it closely. Sometimes
>> the last name has more than one part, e.g. in Ludwig van Beethoven has
>> the "van" all in lower case. There are likely other cases, perhaps Asian
>> surnames (though none come to mind) where it will give you an incorrect
>> result; i.e. just what you asked for but not what you need. So use it
>> judiciously.
>>
>>
>>
>> "Doug" <doug57@www.com> wrote in message
>> news:uY8jneerIHA.1316@TK2MSFTNGP06.phx.gbl...
>>>I have a spread sheet created with Excel 2007. One colum has first and
>>>last names in all caps. How can I convert the first letter for each name
>>>to be caps and the rest of the letters to be lowercase? I want to do the
>>>whole colum at once.
>>>
>>> Thanks
>>>
>>> --
>>> ----------------------------------------------------
>>> This mailbox protected from unsolicited email by Spam Alarm
>>> from Dignity Software http://www.dignitysoftware.com
>>>
>>
>
>

Here is an other way to deal with selected cells anywhere in the worksheet..

' Force explicit variable declaration
Option Explicit

Sub PROPER()
' Variable for selected cells
Dim Cell

' Change all cells in the current selection
For Each Cell In Selection
Cell.Value = Application.WorksheetFunction.PROPER(Cell.Value)
Next

End Sub


"Doug" <doug57@www.com> wrote in message
news:uY8jneerIHA.1316@TK2MSFTNGP06.phx.gbl...
> I have a spread sheet created with Excel 2007. One colum has first and
> last names in all caps. How can I convert the first letter for each name
> to be caps and the rest of the letters to be lowercase? I want to do the
> whole colum at once.
>
> Thanks
>
> --
> ----------------------------------------------------
> This mailbox protected from unsolicited email by Spam Alarm
> from Dignity Software http://www.dignitysoftware.com
>

Just a warning here...........

If you have any formulas in your selected range, the posted code will change
those formulas to values.

Perferable is to leave them alone.

Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
Cell.Formula = Application.Proper(Cell.Formula)
Next
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

On Thu, 8 May 2008 19:31:28 -0700, "Jan Haraldsson" <jan_haraldsson@hotmail.com>
wrote:

>Here is an other way to deal with selected cells anywhere in the worksheet..
>
>' Force explicit variable declaration
>Option Explicit
>
>Sub PROPER()
> ' Variable for selected cells
> Dim Cell
>
> ' Change all cells in the current selection
> For Each Cell In Selection
> Cell.Value = Application.WorksheetFunction.PROPER(Cell.Value)
> Next
>
>End Sub
>
>
>"Doug" <doug57@www.com> wrote in message
>news:uY8jneerIHA.1316@TK2MSFTNGP06.phx.gbl...
>> I have a spread sheet created with Excel 2007. One colum has first and
>> last names in all caps. How can I convert the first letter for each name
>> to be caps and the rest of the letters to be lowercase? I want to do the
>> whole colum at once.
>>
>> Thanks
>>
>> --
>> ----------------------------------------------------
>> This mailbox protected from unsolicited email by Spam Alarm
>> from Dignity Software http://www.dignitysoftware.com
>>

Gord,

Would this amendment get around that probelm or have I just swapped one
error for another?

Sub Proper()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = Application.Proper(Cell.Value)
End If
Next
Application.ScreenUpdating = True
End Sub

In my limited testting it seems to work.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:sfu824p19i24u1l4gtelv6kgeo399tdp6i@4ax.com...
> Just a warning here...........
>
> If you have any formulas in your selected range, the posted code will
> change
> those formulas to values.
>
> Perferable is to leave them alone.
>
> Sub Proper()
> Dim Cell As Range
> Application.ScreenUpdating = False
> For Each Cell In Selection
> Cell.Formula = Application.Proper(Cell.Formula)
> Next
> Application.ScreenUpdating = True
> End Sub
>
>
> Gord Dibben MS Excel MVP
>
> On Thu, 8 May 2008 19:31:28 -0700, "Jan Haraldsson"
> <jan_haraldsson@hotmail.com>
> wrote:
>
>>Here is an other way to deal with selected cells anywhere in the
>>worksheet..
>>
>>' Force explicit variable declaration
>>Option Explicit
>>
>>Sub PROPER()
>> ' Variable for selected cells
>> Dim Cell
>>
>> ' Change all cells in the current selection
>> For Each Cell In Selection
>> Cell.Value = Application.WorksheetFunction.PROPER(Cell.Value)
>> Next
>>
>>End Sub
>>
>>
>>"Doug" <doug57@www.com> wrote in message
>>news:uY8jneerIHA.1316@TK2MSFTNGP06.phx.gbl...
>>> I have a spread sheet created with Excel 2007. One colum has first and
>>> last names in all caps. How can I convert the first letter for each name
>>> to be caps and the rest of the letters to be lowercase? I want to do the
>>> whole colum at once.
>>>
>>> Thanks
>>>
>>> --
>>> ----------------------------------------------------
>>> This mailbox protected from unsolicited email by Spam Alarm
>>> from Dignity Software http://www.dignitysoftware.com
>>>
>
>

That is another routine that I have posted in the past.

Works fine for me and I have not run into any problems with it.


Gord

On Fri, 9 May 2008 18:22:08 +0100, "Sandy Mann" <sandymann2@mailinator.com>
wrote:

>Gord,
>
>Would this amendment get around that probelm or have I just swapped one
>error for another?
>
>Sub Proper()
>Dim Cell As Range
>Application.ScreenUpdating = False
> For Each Cell In Selection
> If Not Cell.HasFormula Then
> Cell.Value = Application.Proper(Cell.Value)
> End If
> Next
>Application.ScreenUpdating = True
>End Sub
>
>In my limited testting it seems to work.