Extracting a last name 2

[itmasterw]

Hi,
I have a list of names in Column A that look like this STEPHEN J DUNN and I ma looking for a way Through a formula or vba code, to copy just the last name to another column. Also, the name sometimes has a midle Intial (or otther names) and sometimes it don't; but the last name is always at the end. Any help would be appreaciated.
Thank You

 

[AnotherHiggins]

Have you looked into Text to Columns?

Data > Text to Columns
tick box beside Delimited
press Next
tick the boxes beside Space, comma and Treat consecutive delimiters as one
press Finished

Just a thought.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[itmasterw]

Thanks, that does work, but because the middle initial or other names I still have to do some coppying to get the last name into one column and I have a long list ot work with. So I was wondering if there is a formula I could use.

 

[coachdan]

Scroll through your list and see what the maximum number of names/initials/etc there are. Insert that amount of columns adjacent to the name field (count the name field in your total - example 4 names = 3 new columns). Then run the text to columns, it should put each part into a column alone. You may have to play around with your delimiters until you get the desired result.

coachdan32

 

[AnotherHiggins]

This is kind of ugly, but it will work as long as you will have a max of three names (two spaces in the text string).
[COLOR=blue white] =RIGHT(SUBSTITUTE(A2," ","%",2),LEN(SUBSTITUTE(A2," ","%",2))-(IF(ISERROR(SEARCH("%",SUBSTITUTE(A2," ","%",2))),SEARCH(" ",SUBSTITUTE(A2," ","%",2)),SEARCH("%",SUBSTITUTE(A2," ","%",2)))))[/color]

I'm sure there's an easier way that I'm overlooking. Perhaps someone can provide a shorter solution.

I need to get some work (that I get paid for) done.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[itmasterw]

Thanks, this looks great.