• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Seperate last name from records

M

marcvanderpeet12

Member
Dear all,


I'm looking for a way to seperate the last name of a total name in a colum. I'm already familiar with the text to column option but thing is that then i get the name like this:


First name; between verb; last name


And I only want the last name.


It is list with Dutch names with a lot of "between verbs" and do not want to delete all of them.


Any tips would be highly appreciated!


Dear regards,


Marc
 
Hi Marc


Some examples would have given us a sporting chance. Something like here are my dutch names


Full Name Last Names

Berg de Graph de Graph

Rosa De Keizer De Keizer

Frederick Vos Vos

Peter Van Der Hoegen Band ???????


I am not that familiar with Dutch names so I can only guess if this is what you want to see. The last one is a Doozy though as it presents a few challenges. I feel like I should be the one asking the question.


Take care


Smallman
 
Hi Marc ,


Can I suggest an option ?


You might have a list running into hundreds , but the number of last names may be much fewer ; is it possible you can make a list of these last names ; what can then be done is to search for these last names in the main list of full names , and return the last name which matches.


Narayan
 
Ok I'll provide some examples:


I have this


Name

Marc van der Peet

Jonas Jansen

Piet van Straten


Through text to column I can get this:


Marc van der Peet

Jonas Jansen

Piet Van Straten


But i'm looking for a way to get all the last names into one column. Does this make it more clear?


Dear regards,


Marc
 
Hmm, all the tabs in my post do not work. I mean:


Through text to column I can get this:


Marc <next colum> van <next colum> der <next colum> Peet

Jonas <next colum> Jansen

Piet <next colum> Van <next colum> Straten


But i'm looking for a way to get all the last names into one column. Does this make it more clear?


Dear regards,


Marc
 
Marc


I am not dutch as such I have no idea of the last name of a dutch person. In your example is the last name.


Van Der Peet


Or


Peet


No idea in the world. That is for you to tell us. I thought i was clear in my first post. I must have missed something when I asked you to provide some examples of raw data and output data.


Smallman
 
Sorry for being unclear.


Anyway, the last name is "Peet". So i'm looking for a VBA script /excel tool which does something like looking at the total name (Marc van der Peet) and then producing the last word ("Peet") in the next column.


And because i have 20000 names for i have to do this i cant do it with a formula like (right, 4).


Did i make my question clear now smallman?


Thanks in advance.


Dear regards,


Marc
 
Hi Marc


That is finally clear. In future make your posts like the above. It clearly sets out what you have and what you are looking for. Ask yourself in future, does my post achieve these two objectives.


You will be able to get a reasonable answer from the above now.


Take care


Smallman
 
Hi Marc


Give the following a try. Should get you the sirname.


=RIGHT(A1,FIND(" ",A1)-1)


Take it easy


Smallman
 
I think, Smallman has not tested his formula on all the data OP posted ;).


If you are after last word then try this formula.

=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),1)+1,99)


or


=MID(A1,LOOKUP(2,1/(MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)=" "),ROW($A$1:INDEX(A:A,LEN(A1))))+1,99)


or


=MID(A1,LOOKUP(2,FIND(" ",MID(A1,ROW($A$1:$A$99),1),1),ROW($A$1:$A$99))+1,99)
 
Marc


I only checked it on the first name in your list as stated above. A rework of my formula to get the last name in the string.


=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))


Take care


Smallman
 
Hello Marc,


Here is one more way. Make sure there are no extra space at the end


Select the range, press CTRL+f to activate Find/Replace.


Find What: * <then press space bar>


Then click Replace All


Haseeb
 
You must log in or register to reply here.
Back
Top