Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have names in one column. i would like to split last names to another column.
Some names have 2 first names. exp: mary anne smith, susan smith, etc... just last names to the another column. Please suggest! |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Assuming your list starts in a1 put this in B1 and drag down
=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)) Mike "mtin" wrote: I have names in one column. i would like to split last names to another column. Some names have 2 first names. exp: mary anne smith, susan smith, etc... just last names to the another column. Please suggest! |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I'm sure someone has a prettier way, but...
=IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),MID(A1,FIND(" ",A1),LEN(A1)-FIND(" ",A1)+1),MID(A1,FIND(" ",A1,FIND(" ",A1)+1),LEN(A1)-FIND(" ",A1,FIND(" ",A1)))) -- -John Please rate when your question is answered to help us and others know what is helpful. "mtin" wrote: I have names in one column. i would like to split last names to another column. Some names have 2 first names. exp: mary anne smith, susan smith, etc... just last names to the another column. Please suggest! |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Fri, 7 Nov 2008 06:21:01 -0800, mtin wrote:
I have names in one column. i would like to split last names to another column. Some names have 2 first names. exp: mary anne smith, susan smith, etc... just last names to the another column. Please suggest! To extract the last name: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) To remove the last name from the original string, assuming the last name is in B1, and the full name is in A1: =SUBSTITUTE(A1,B1,"",(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Splitting cells | Excel Discussion (Misc queries) | |||
Splitting | New Users to Excel | |||
splitting Last Name, First Name | Excel Discussion (Misc queries) | |||
Splitting Numbers | Excel Discussion (Misc queries) | |||
Splitting cells - please help! | Excel Worksheet Functions |