Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Employee Names in Capital Letters
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Employee Names in Capital Letters
=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) "Diane Walker" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Employee Names in Capital Letters
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. "Diane Walker" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Employee Names in Capital Letters
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 "Diane Walker" wrote in message : 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Employee Names in Capital Letters
LOL, forgot PROPER
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Alan" wrote in message ... 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. "Diane Walker" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Employee Names in Capital Letters
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 --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Diane Walker" wrote ... I got [have] 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing capital letters | Excel Discussion (Misc queries) | |||
Creating Names Baffling Problem | Excel Discussion (Misc queries) | |||
capital letters to lowercase | Excel Discussion (Misc queries) | |||
Capital Letters | Excel Worksheet Functions | |||
Auto change font to 'capital letters' | Excel Worksheet Functions |