Split the surname from a name?

Split the surname from a name?

Didn't find your answer?

I have a column of names in Excel in the style "Mr A B C Smith". The number of initials varies between records and in some cases it is "Mr Albert Smith". I can seperate the title by finding the first space and using the Left command, but how can I separate the surname?

I'm sure this should be doable but I can't think how!

(By the way, I didn't create the data in this format in the first place) Thanks
Fraser

Replies (6)

Please login or register to join the discussion.

avatar
By AnonymousUser
25th Apr 2006 21:44

Slightly more stable version:
=RIGHT(A1,LEN(A1)-MAX(IF(MID(LEFT(A1,255),ROW(INDIRECT("$1:$255")),1)=" ",ROW(INDIRECT("$1:$255")))))

(array-entered, of course)

Thanks (0)
avatar
By AnonymousUser
25th Apr 2006 16:47

or, if you don't like using VBA:
=RIGHT(A1,LEN(A1)-MAX(IF(MID(LEFT(A1,255),ROW($1:$255),1)=" ",ROW($1:$255))))

Array-enter the formula (Control+shift+enter)

(In this example the name is contained in cell A1)

(always fond of a challenge!)

Thanks (0)
avatar
By listerramjet
25th Apr 2006 15:01

if you don't like VBA then tough!
if you could find the position of the last space in the name string then you can crack this with the =right function

you can find the position of the last space by creating a user defined function (VBA I'm afraid)

Function SF(tWh As String) As Long
For c = 1 To Len(tWh)
If Mid(tWh, c, 1) = " " Then SF = c
Next c
End Function


then if the names are in column a the formula
=RIGHT(A1,LEN(A1)-SF(A1))

should do the trick

Thanks (0)
avatar
By David Carter
30th Apr 2006 21:36

text to columns
A formula and VBA-free way would be: Highlight the column, then Data - Text to Columns. Choose Delimited - Space. This will split each name into separate columns with the surname in the far right column.

Click onto column H. Sort AZ. Any surnames will appear at the top of the column. Copy them left into column G.

Now sort AZ on column G. Surnames will appear at the top. Copy them into column F.

Keeping working from right to left and eventually you will end up with all the surnames in column A (I think)

Thanks (0)
avatar
By AnonymousUser
26th Apr 2006 07:51

Refinement:
=RIGHT(TRIM(A1),LEN(TRIM(A1))-MAX(IF(MID(LEFT(TRIM(A1),10),ROW(INDIRECT("1:10")),1)=" ",ROW(INDIRECT("1:10")))))
(array-entered)

Caters for the possibility of an inadvertent space entered after the surname.

Reduced 255 to a less processor-hungry 10, on the grounds that it is reasonably safe to assume no space will arise after the 10th place in the name. (Actually not that safe an assuption, perhaps change it to about 20).

Thanks (0)
avatar
By AnonymousUser
03rd May 2006 16:32

Thanks...
I have gone the VBA route (never having used VBA before, a quick search on the help produced an idiot's guide) and it worked fine, thanks.

Fraser

Thanks (0)