Change Case for Names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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"))
 
And VBA has its own built-in function, too.

See StrConv in VBA's help for more info.
 
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
 
A little more detail:
Sub Conv()
For Each cell In Selection
cell.Value = StrConv(cell, 3)
Click to expand...

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

cell.Value = StrConv(cell, vbProperCase)

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

cell.Formula = StrConv(cell, vbProperCase)


Gord Dibben MS Excel MVP
 
oops

Meant to post this instead.

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


Gord
 
A little more detail:
I prefer to not change any formulas in the selection to values.

cell.Formula = StrConv(cell, vbProperCase)
Click to expand...

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
 
Back
Top