Extract surname from forenames

[Charlotte Howard]

Hello,

I have a long list of names that I want to break down into
Surname/Forenames. I have used an IF statement to get out the surname after
using Text to Cols to breakdown the data. I now need to separate out all
other names - any idea how to do this?
Some sample data:

Surname Forenames
AGNEW ROBERT E AGNEW
HYDE W H S HYDE
KEYS RICHARD KEYS
LYONS WILLIAM JOHN LYONS
PRICE HAROLD PRICE
BOYD SUSAN ELIZABETH ANN BOYD

Thanks,

Charlotte

 

[Stefi]

Try this:
Select column containing whole names
Data>Text to columns>choose Separated option>Next>Check Spaces as separator

(provided all name element are separated by spaces as shown in your example)

Regards,
Stefi

„Charlotte Howard†ezt írta:

 

[Pete_UK]

If you use Data | Text-to-columns on your sample data using space as
delimiter, then clearly you will get some surnames in the second,
third or fourth columns beyond your first name (assume this to be in
A1). You could put a formula like this in a helper column to get the
surname:

=IF(E1<>"",E1,IF(D1<>"",D1,IF(C1<>"",C1,B1)))

and copy this down. This will cope with up to 5 names and/or initials
and will always return the final name in the cells B to E, assuming
column A is a forename.

Hope this helps.

Pete

 

[Charlotte Howard]

Hi Stefi,

Text-to-Columns will not work in this instance. Some people only have 1
forename, others have 4 (see below for result of using text to cols).
Largely they only have 1 surname, so I managed to extract that using
=IF(O3<>0,O3,IF(N3<>0,N3,IF(M3<>0,M3,IF(L3<>0,L3,IF(K3<>0,K3,IF(J3<>0,J3,IF(I3<>0,I3,"false")))))))

I now need to get all the forenames in one column.

ROBERT E AGNEW
W H S HYDE
RICHARD KEYS
CHARLES KNIPE
WILLIAM JOHN LYONS
HAROLD PRICE
SUSAN ELIZABETH ANN BOYD

Thanks!
Charlotte

 

[Charlotte Howard]

Hi Pete,
I used a similar IF statement to extract the surnames, but I now need to get
the forenames into a column on their own.
I have a Column called Surname with the surname, and one called name with
the entire name - fore & surnames - I have also used Text to Cols with the
space delimiter to separate out all names.

I need to be able to keep all forenames (some people have as many as four)
in a column together.

Thanks for your help

Charlotte

 

[Pete_UK]

Assume your surnames are in column A and entire names are in column B
as shown in your example. Put this formula in C1:

=LEFT(B1,LEN(B1)-LEN(A1)-1)

If you might have leading/trailing or multiple spaces, then you can
wrap TRIM( ... ) around the references to A1 and B1. Then you can copy
the formula down.

Another way would have been to use the SUBSTITUTE function.

Hope this helps.

Pete

 

[Charlotte Howard]

Brilliant Pete - here I was trying IF statements - and all I needed was
either Substitute or Left!

Thanks a mill

Charlotte

 

[Pete_UK]

You're welcome, Charlotte - thanks for feeding back.

Pete