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

Pete

On Mar 20, 4:11*pm, Charlotte Howard
wrote:
Brilliant Pete - here I was trying IF statements - and all I needed was
either Substitute or Left!

Thanks a mill

Charlotte



"Pete_UK" wrote:
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

On Mar 20, 3:43 pm, Charlotte Howard
wrote:
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" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -