Well I don't know how elegant the programing is but I have come up with the
following that seems to work.

Name in A1,Formulars in B1 & C1

b1=IF(ISNUMBER(FIND("Mc ",A1,1)),MID(A1,(FIND("Mc
",A1,1)),255),MID(A1,FIND(CHAR(1),SUBSTITUTE(A 1,"
",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255))

c1=SUBSTITUTE(A1,B1,"")

May be of use to someone else.

Mal


"Mal" wrote in message
d.com...
One further point.
The formular from James works perfectly with the exception of names such
as Mc Mahon. This must be because of the space between the Mc"space"Mahon.
Is there any way to account for these Mc "space" names?
Thanks,
Mal

"Mal" wrote in message
nd.com...
Thanks for that James. Works perfectly.

Mal

"James Ravenswood" wrote in message
...
On Dec 27, 4:38 pm, "Mal" wrote:
I have a list of names in Excel 2003, Last Name in column A first name
in B
sorted in alfa order.
I get a list from an external source that includes names that I wish to
match to my list.
The problem is that the format of the supplied list is completely
different
to my list and I cannot get the source to change their format.
e.g.
My List
A B
Black Bill
Brown J
Jones Amy
Smith John
White Sandra

The supplied list is in Excel in one column as follows and not sorted:

Amy Jones
B. Black
S J White
John J Smith
Joe Brown

The only constants are the Last Name and the punctuation and spaces
vary.

I was thinking of maybe somehow sorting backwards on the last name?

Ant help appreciated.

Thanks,

Mal

Assume your supplied list is in column A. The first step is to get
the last names in a separate column. In B1 enter:
=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))))+1,255) and copy down

The second step is to get the other material in a separate column. In
C1 enter:
=SUBSTITUTE(A1,B1,"") and copy down

So if A1 contained;
John J. Smith
B1 wouild display:
Smith
and C1 would display:
John J.

Now you can sort and try to match them up.