 |
 | 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 2008string manipulation, pulling out last name from column
Hello all, I have an Excel spreadsheet with a list of wedding invitees, but the names are in the improper format: "Lastname, Husbandfirstname and Wifefirstname" I need to extract out the "Lastname, " into a new column, and then clean up that column by deleting the ending comma and space (which is ", "). I think that is clear. I really appreciate your help. I did some basic searching to try to find an answer, and I learned some things about string manipulation, but wasn't successful tweaking the formula to do what I want. Also, I must say I am not clear as to how to APPLY the formula to an existing column. Thank you for your time. AP This formula should work for you... =LEFT(A1,FIND(",",A1)-1) Rick > Hello all, > I have an Excel spreadsheet with a list of wedding invitees, but the [quoted text clipped - 14 lines] > Thank you for your time. > AP =REPLACE(A1,FIND(",",A1),99,"") > Hello all, > I have an Excel spreadsheet with a list of wedding invitees, but the [quoted text clipped - 14 lines] > Thank you for your time. > AP Thank you so much for your help! My last question is, how do I now delete the lastname and the comma and space from the column? For example, you've helped me succeed in parsing out the last name into its own column... How do I then take: "Lastname, Husbandfirstname and Wifefirstname" And delete the "Lastname, " from within that column so that only "Husbandfirstname and Wifefirstname" remain? Again - I am very grateful for your time. AP =MID(A1,FIND(" ",A1)+1,99) > Thank you so much for your help! My last question is, how do I now > delete the lastname and the comma and space from the column? For [quoted text clipped - 8 lines] > Again - I am very grateful for your time. > AP Thank you SO much. We are good to go. AP >Hello all, >I have an Excel spreadsheet with a list of wedding invitees, but the [quoted text clipped - 14 lines] >Thank you for your time. >AP You could use the Data/Text to Column wizard specifying <comma> as the separator. But " husbandfirstname and wifefirstname" will remain in the target cell with a leading space. So you can "clean that up" by using the function, in somecell, of =TRIM(A2). For example, with data in A2:A100 Select the Range Select Data/Text to Columns Delimited <NEXT> ` Select Comma <FINISH> Then C2: =TRIM(B2) Fill down to C100 Select C2:C100 Edit/Copy Edit/Paste Special/Values You can then delete column B --ron |
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.