Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Case for Names
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Case for Names
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Case for Names
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Case for Names
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Case for Names
forgot about that one, thanks, dave.
-- Gary "Dave Peterson" wrote in message ... 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Case for Names
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Case for Names
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Case for Names
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\)" 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Case for Names
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\)" 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Case for Names
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can I change the cap letters of student names to lower case | Excel Discussion (Misc queries) | |||
change data of entire column from small case to upper case | Excel Worksheet Functions | |||
How do I change the case from all caps to proper for names I have in columns E, F, G? | Excel Discussion (Misc queries) | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) | |||
How to use formula auditing to change upper case to Title Case. | Excel Worksheet Functions |