 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Format 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. maybe this in b1 and dragged down =MID(A1,FIND(",",A1,1)+2,LEN(A1))&" "&LOWER(LEFT(A1,FIND(",",A1,1)-1)) Mike > Can someone please help with a formula? > [quoted text clipped - 5 lines] > Thanks in advance! > - D. Thanks Mike but the first letter of the last name also goes lower case. How do I get the first letter to remain capitalized? > maybe this in b1 and dragged down > [quoted text clipped - 11 lines] > > Thanks in advance! > > - D. It's all to do with spaces, see your other reponse where spaces are trimmed out. Mike > Thanks Mike but the first letter of the last name also goes lower case. How > do I get the first letter to remain capitalized? [quoted text clipped - 14 lines] > > > Thanks in advance! > > > - D. One way: =PROPER(TRIM(MID(A1,FIND(",",A1)+1,255) & " " & LEFT(A1,FIND(",",A1)-1))) > Can someone please help with a formula? > [quoted text clipped - 5 lines] > Thanks in advance! > - D. PERFECT! Thanks so much! You guys rock. I'd have never figured that out on my own. > One way: > [quoted text clipped - 10 lines] > > Thanks in advance! > > - D. If you come back to this thread, here is a shorter alternative method... =PROPER(MID(A7&" "&A7,FIND(" ",A7)+1,LEN(A7)-1)) Rick > Can someone please help with a formula? > [quoted text clipped - 7 lines] > Thanks in advance! > - D. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.