extracting surnames

[swfarm]

Hi Guys

Ive been using this formula to extract football players surnames which works well
=RIGHT(D2,LEN(D2)-FIND(" ",D2,1))

However some of these pesky players only have a surname

ie. Fred for man united
or
Jonny for wolves

is it possible to manipulate the formula to suit this scenario to show Fred and Jonny as the surname despite not having a first name listed


All help welcomed
thanks in advance

[Richard Buttrey]

You really need to upload the data you're working with and add the results you expect to see. At the moment we can only guess what it looks like.

One way might be to detect how many spaces there are in the string and slice it as necessary but we do need to see the data.

[swfarm]

hi Richard

thanks for your reply
it really is standard data
ie

Fred
Wayne Rooney
David Beckham
Jonny
Alan Shearer

one space between first name and surname

however as you can see there is no space before fred or after and the same with Jonny

[Phuocam]

Try
=IFERROR(RIGHT(D2,LEN(D2)-FIND(" ",D2,1)),D2)

or

=TRIM(RIGHT(SUBSTITUTE(" "&D2," ",REPT(" ",50)),50))

[swfarm]

perfection

Thanks So much Phuocam
much appreciated

[Richard Buttrey]

..another option

Please Login or Register  to view this content.