remove characters from a string

[p4nny]

Hi

I have the following info in cells a2:a5

Andrew Lee Jones
Ken David Thompson
Chris Smith
David Stephen Scott


I would like to keep the last name.. So keep, Jones, Thompson, smith, Scott

Please coud you show me the best way of doing this

 

[XOR LX]

Hi.

Is there any way of knowing whether, for a given name, the second-to-last word is that person's middle name or part of the surname?

For example, in "Robin van Persie", "van" is actually part of his last name, so you should keep "van Persie" in that case.

However, in "David Stephen Scott", you would only keep "Scott".

How can we tell Excel to keep the second-to-last name in certain cases and not in others? Or would you be happy to take the chance that you will never have any examples like "van Persie" in your data and so always just take the last word?

Regards

 

[p4nny]

I understand where you are coming from and thanks for pointing that out because I hadn't thought of that.

In this scenrio, it is purely the surname.

Cheers

 

[XOR LX]

This is visually shorter:

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

though this, despite being longer, may actually calculate more quickly:

=RIGHT(A1,LEN(A1)-FIND("ζ",SUBSTITUTE(A1," ","ζ",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Regards

 

[hiker95]

p4nny,

How about something like this:

Excel 2007
	A	B
1
2	Andrew Lee Jones	Jones
3	Ken David Thompson	Thompson
4	Chris Smith	Smith
5	David Stephen Scott	Scott
6
Sheet1

Cell Formulas
Range	Formula
B2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))

The formula in cell B2, copied down:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))