Extract surname from forenames

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Charlotte Howard - 20 Mar 2008 14:36 GMT

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

Reply to this Message

Stefi - 20 Mar 2008 16:00 GMT

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:

> Hello,
>
[quoted text clipped - 15 lines]
>
> Charlotte

Reply to this Message

Charlotte Howard - 20 Mar 2008 16:33 GMT

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

> Try this:
> Select column containing whole names
[quoted text clipped - 4 lines]
> Regards,
> Stefi

Reply to this Message

Pete_UK - 20 Mar 2008 16:30 GMT

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

> Hello,
>
[quoted text clipped - 15 lines]
>
> Charlotte

Reply to this Message

Charlotte Howard - 20 Mar 2008 16:43 GMT

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

> 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,
[quoted text clipped - 11 lines]
>
> Pete

Reply to this Message

Pete_UK - 20 Mar 2008 16:56 GMT

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

> Hi Pete,
> I used a similar IF statement to extract the surnames, but I now need to get
[quoted text clipped - 27 lines]
>
> - Show quoted text -

Reply to this Message

Charlotte Howard - 20 Mar 2008 17:11 GMT

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

Thanks a mill

Charlotte

> Assume your surnames are in column A and entire names are in column B
> as shown in your example. Put this formula in C1:
[quoted text clipped - 42 lines]
> >
> > - Show quoted text -

Reply to this Message

Pete_UK - 20 Mar 2008 18:07 GMT

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

Pete

> Brilliant Pete - here I was trying IF statements - and all I needed was
> either Substitute or Left!
[quoted text clipped - 51 lines]
>
> - Show quoted text -

Reply to this Message