Employee Names in Capital Letters

[Diane Walker]

I got this file that has employee names. However, the names are all in
capital letters. Is there a way to break the name in the appropriate case?
For example, the name is JOE SMITH. I would like the name to be displayed
as Joe Smith. Thanks.

 

[Bob Phillips]

=LEFT(A1,1)&LOWER(MID(A1,2,FIND(" ",A1)-1))&MID(A1,FIND("
",A1)+1,1)&LOWER(RIGHT(A1,LEN(A1)-FIND(" ",A1)-1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Alan]

With JOE SMITH in A1 try
=PROPER(A1), drag down to the end of the list and Copy > Paste Special >
Values to lose the formulas,
Regards,
Alan.

 

[Jim May]

You could create a temporary "helper Column" to CONVERT
The Names; Afterwards do a Copy - Paste-Special-Values
Of the formula (to convert back to a Value) -- then replace
Your original ALLCAPS data.

A1 = JOE SMITH
B1 = =Proper(A1) >> Should display Joe Smith

HTH

 

[Bob Phillips]

LOL, forgot PROPER

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[David McRitchie]

Hi Diane,
(Reusing reply just used in another thread)

You need a macro to do what you want. A function can only return
a value, so if you don't want the little dance routine to convert formulas
to values and remove the original column, you would need to use a
macro.

With a macro all you need to do is select the cells to be converted.
If the macro is properly written it will restrict itself to the used range
(one way is with SpecialCells), then you can select entire columns
or the entire worksheet. If it is not properly written converting a
single column could take 3 minutes (65536 cells on 600mHz with 128MB RAM).
You should hardly notice converting 4000 cells in a column.

Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm#proper