| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Format Name
Can someone please help with a formula?
In Column A I have names formatted as SMITH, John. In Column B I need John Smith. Would someone please help with a formula that would switch the names, get rid of the comma, and change the case to lower case of the last name except for the first letter? Thanks in advance! - D. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Format Name
maybe this in b1 and dragged down
=MID(A1,FIND(",",A1,1)+2,LEN(A1))&" "&LOWER(LEFT(A1,FIND(",",A1,1)-1)) Mike "Dax Arroway" wrote: Can someone please help with a formula? In Column A I have names formatted as SMITH, John. In Column B I need John Smith. Would someone please help with a formula that would switch the names, get rid of the comma, and change the case to lower case of the last name except for the first letter? Thanks in advance! - D. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Format Name
One way:
=PROPER(TRIM(MID(A1,FIND(",",A1)+1,255) & " " & LEFT(A1,FIND(",",A1)-1))) In article , Dax Arroway wrote: Can someone please help with a formula? In Column A I have names formatted as SMITH, John. In Column B I need John Smith. Would someone please help with a formula that would switch the names, get rid of the comma, and change the case to lower case of the last name except for the first letter? Thanks in advance! - D. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Format Name
Thanks Mike but the first letter of the last name also goes lower case. How
do I get the first letter to remain capitalized? "Mike H" wrote: maybe this in b1 and dragged down =MID(A1,FIND(",",A1,1)+2,LEN(A1))&" "&LOWER(LEFT(A1,FIND(",",A1,1)-1)) Mike "Dax Arroway" wrote: Can someone please help with a formula? In Column A I have names formatted as SMITH, John. In Column B I need John Smith. Would someone please help with a formula that would switch the names, get rid of the comma, and change the case to lower case of the last name except for the first letter? Thanks in advance! - D. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Format Name
It's all to do with spaces, see your other reponse where spaces are trimmed
out. Mike "Dax Arroway" wrote: Thanks Mike but the first letter of the last name also goes lower case. How do I get the first letter to remain capitalized? "Mike H" wrote: maybe this in b1 and dragged down =MID(A1,FIND(",",A1,1)+2,LEN(A1))&" "&LOWER(LEFT(A1,FIND(",",A1,1)-1)) Mike "Dax Arroway" wrote: Can someone please help with a formula? In Column A I have names formatted as SMITH, John. In Column B I need John Smith. Would someone please help with a formula that would switch the names, get rid of the comma, and change the case to lower case of the last name except for the first letter? Thanks in advance! - D. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Format Name
PERFECT! Thanks so much! You guys rock. I'd have never figured that out on
my own. "JE McGimpsey" wrote: One way: =PROPER(TRIM(MID(A1,FIND(",",A1)+1,255) & " " & LEFT(A1,FIND(",",A1)-1))) In article , Dax Arroway wrote: Can someone please help with a formula? In Column A I have names formatted as SMITH, John. In Column B I need John Smith. Would someone please help with a formula that would switch the names, get rid of the comma, and change the case to lower case of the last name except for the first letter? Thanks in advance! - D. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Format Name
If you come back to this thread, here is a shorter alternative method...
=PROPER(MID(A7&" "&A7,FIND(" ",A7)+1,LEN(A7)-1)) Rick "Dax Arroway" wrote in message ... Can someone please help with a formula? In Column A I have names formatted as SMITH, John. In Column B I need John Smith. Would someone please help with a formula that would switch the names, get rid of the comma, and change the case to lower case of the last name except for the first letter? Thanks in advance! - D. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
| Adding time in 24 hour format to produce hours in decimal format | Excel Worksheet Functions | |||
| Decide comment format 'globally'? Restore format with ws_change? | Excel Discussion (Misc queries) | |||
| Replace million-billion number format to lakhs-crores format | Excel Discussion (Misc queries) | |||
| how to format excel format to text format with separator "|" in s. | New Users to Excel | |||
Linear Mode
