I have a data dump with names. Since the users inputs their name, the case
of the name varies. I would like to clean the names up to make them look
nice. For example, the names are typically one of these three:

Jeff (this is good)
jeff (not good)
JEFF (not good)

How can I clean them up so the first letter is always capital and the rest
are lower case?

I hope I can do this.. Thanks!
--
Jeff

Try:

=Proper(a1)

Mike

"Jeff" wrote:

> I have a data dump with names. Since the users inputs their name, the case
> of the name varies. I would like to clean the names up to make them look
> nice. For example, the names are typically one of these three:
>
> Jeff (this is good)
> jeff (not good)
> JEFF (not good)
>
> How can I clean them up so the first letter is always capital and the rest
> are lower case?
>
> I hope I can do this.. Thanks!
> --
> Jeff

you didn't mention where the data was, but if you want to use vba to correct the
case, try this

Range("A1") = Application.Proper(Range("a1"))

--


Gary


"Jeff" <(E-Mail Removed)> wrote in message
news54ECD69-3ACF-4F49-ABCB-(E-Mail Removed)...
>I have a data dump with names. Since the users inputs their name, the case
> of the name varies. I would like to clean the names up to make them look
> nice. For example, the names are typically one of these three:
>
> Jeff (this is good)
> jeff (not good)
> JEFF (not good)
>
> How can I clean them up so the first letter is always capital and the rest
> are lower case?
>
> I hope I can do this.. Thanks!
> --
> Jeff

And VBA has its own built-in function, too.

See StrConv in VBA's help for more info.

Gary Keramidas wrote:
>
> you didn't mention where the data was, but if you want to use vba to correct the
> case, try this
>
> Range("A1") = Application.Proper(Range("a1"))
>
> --
>
> Gary
>
> "Jeff" <(E-Mail Removed)> wrote in message
> news54ECD69-3ACF-4F49-ABCB-(E-Mail Removed)...
> >I have a data dump with names. Since the users inputs their name, the case
> > of the name varies. I would like to clean the names up to make them look
> > nice. For example, the names are typically one of these three:
> >
> > Jeff (this is good)
> > jeff (not good)
> > JEFF (not good)
> >
> > How can I clean them up so the first letter is always capital and the rest
> > are lower case?
> >
> > I hope I can do this.. Thanks!
> > --
> > Jeff

--

Dave Peterson

forgot about that one, thanks, dave.

--


Gary


"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> And VBA has its own built-in function, too.
>
> See StrConv in VBA's help for more info.
>
> Gary Keramidas wrote:
>>
>> you didn't mention where the data was, but if you want to use vba to correct
>> the
>> case, try this
>>
>> Range("A1") = Application.Proper(Range("a1"))
>>
>> --
>>
>> Gary
>>
>> "Jeff" <(E-Mail Removed)> wrote in message
>> news54ECD69-3ACF-4F49-ABCB-(E-Mail Removed)...
>> >I have a data dump with names. Since the users inputs their name, the case
>> > of the name varies. I would like to clean the names up to make them look
>> > nice. For example, the names are typically one of these three:
>> >
>> > Jeff (this is good)
>> > jeff (not good)
>> > JEFF (not good)
>> >
>> > How can I clean them up so the first letter is always capital and the rest
>> > are lower case?
>> >
>> > I hope I can do this.. Thanks!
>> > --
>> > Jeff
>
> --
>
> Dave Peterson

Hi Jeff,

A little more detail:

Sub Conv()
For Each cell In Selection
cell.Value = StrConv(cell, 3)
Next cell
End Sub

Cheers,
Shane Devenshire


"Jeff" wrote:

> I have a data dump with names. Since the users inputs their name, the case
> of the name varies. I would like to clean the names up to make them look
> nice. For example, the names are typically one of these three:
>
> Jeff (this is good)
> jeff (not good)
> JEFF (not good)
>
> How can I clean them up so the first letter is always capital and the rest
> are lower case?
>
> I hope I can do this.. Thanks!
> --
> Jeff

> A little more detail:
>
> Sub Conv()
> For Each cell In Selection
> cell.Value = StrConv(cell, 3)

I always find using the built-in VB constants makes reading a statement
easier...

cell.Value = StrConv(cell, vbProperCase)

Rick


> Next cell
> End Sub

I prefer to not change any formulas in the selection to values.

cell.Formula = StrConv(cell, vbProperCase)


Gord Dibben MS Excel MVP

On Sat, 16 Jun 2007 15:56:39 -0400, "Rick Rothstein \(MVP - VB\)"
<(E-Mail Removed)> wrote:

>> A little more detail:
>>
>> Sub Conv()
>> For Each cell In Selection
>> cell.Value = StrConv(cell, 3)
>
>I always find using the built-in VB constants makes reading a statement
>easier...
>
> cell.Value = StrConv(cell, vbProperCase)
>
>Rick
>
>
>> Next cell
>> End Sub

oops

Meant to post this instead.

cell.Formula = Application.Proper(cell.Formula)


Gord

On Sat, 16 Jun 2007 17:21:49 -0700, Gord Dibben <gorddibbATshawDOTca> wrote:

>I prefer to not change any formulas in the selection to values.
>
>cell.Formula = StrConv(cell, vbProperCase)
>
>
>Gord Dibben MS Excel MVP
>
>On Sat, 16 Jun 2007 15:56:39 -0400, "Rick Rothstein \(MVP - VB\)"
><(E-Mail Removed)> wrote:
>
>>> A little more detail:
>>>
>>> Sub Conv()
>>> For Each cell In Selection
>>> cell.Value = StrConv(cell, 3)
>>
>>I always find using the built-in VB constants makes reading a statement
>>easier...
>>
>> cell.Value = StrConv(cell, vbProperCase)
>>
>>Rick
>>
>>
>>> Next cell
>>> End Sub

>>> A little more detail:
>>>
>>> Sub Conv()
>>> For Each cell In Selection
>>> cell.Value = StrConv(cell, 3)
>>
>>I always find using the built-in VB constants makes reading a statement
>>easier...
>>
>> cell.Value = StrConv(cell, vbProperCase)
>>
>I prefer to not change any formulas in the selection to values.
>
> cell.Formula = StrConv(cell, vbProperCase)

Actually, I wasn't commenting on the technique that Shane posted, only his
use of a "magic number". VBA has an enormous amount of predefined constants
available for the programmer and my personal preference is to use them over
their numerical values. The name of these constants are usually
self-documenting making reading and/or changing one's code much easier in
later coding sessions.

Rick