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.
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
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)