 |
 | 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 / New Users / December 2007Split Names
How can we split the last names to another cell ? Als if we have Mr & mrs john doe mike how can we split the title ? hi you might try using text to columns. on the menu bar.... Data>text to columns...>follow the wizzard. If your have a lot of names of varing lengths, with and without titles, you may have problems. you may need to group name of similar length and title so that you can parce the data in blocks or groups. otherwise you may have to do i one name at a time. also when parcing data, make sure you have enough blank cells to the right of the data you are parcing because this function will overwrite data to the right. regards FSt1 > How can we split the last names to another cell ? > Als if we have Mr & mrs john doe mike how can we split the title ? hi again, afterthought. if this is the first time you are using this feature, make a copy of your name list just in case. Regards FSt1 > hi > you might try using text to columns. [quoted text clipped - 13 lines] > > How can we split the last names to another cell ? > > Als if we have Mr & mrs john doe mike how can we split the title ? Data>Text to Columns would be sufficient for just splitting out a firstname and lastname. For more complex splitting see Chip Pearson's site. http://www.cpearson.com/excel/FirstLast.htm Note the downloadable workbook examples. Gord Dibben MS Excel MVP >How can we split the last names to another cell ? >Als if we have Mr & mrs john doe mike how can we split the title ? This doen't seem to work because it's to complicated please just answer how can we remove thw last word of whole column > How can we split the last names to another cell ? > Als if we have Mr & mrs john doe mike how can we split the title ? Here's a text formula. With names starting in A1, enter this in B1: =RIGHT(A1,LEN(A1)-SEARCH("^^^",SUBSTITUTE(A1," ","^^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) And copy down as needed.  SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > This doen't seem to work because it's to complicated please just answer how > can we remove thw last word of whole column > > > How can we split the last names to another cell ? > > Als if we have Mr & mrs john doe mike how can we split the title ? The line wrap may present a problem. Try this if you have trouble with the first formula: =RIGHT(A1,LEN(A1)-FIND("^^^",SUBSTITUTE(A1," ","^^^", LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Here's a text formula. > With names starting in A1, enter this in B1: [quoted text clipped - 9 lines] > > > How can we split the last names to another cell ? > > > Als if we have Mr & mrs john doe mike how can we split the title ? See this add-in It will insert a new column in your table with a formula for you Look on the text tab http://www.rondebruin.nl/datarefiner.htm SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > This doen't seem to work because it's to complicated please just answer how > can we remove thw last word of whole column > >> How can we split the last names to another cell ? >> Als if we have Mr & mrs john doe mike how can we split the title ? =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) > This doen't seem to work because it's to complicated please just answer how > can we remove thw last word of whole column > > > How can we split the last names to another cell ? > > Als if we have Mr & mrs john doe mike how can we split the title ? Nice! SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) > [quoted text clipped - 3 lines] > > > How can we split the last names to another cell ? > > > Als if we have Mr & mrs john doe mike how can we split the title ?
|
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.