Formula

[dazoloko]

I have a download into Excel which contains names, however the full name is
in one cell eg

Mr J Smith
Mrs S Jones
Mr A B Thompson

I can extract the title, ie Mr and I can extract the Last name, ie Smith
using formulas. Where Im hitting a wall is where there is more than one
inititial. In effect, I am looking for a formula which will extract all text
between the first space in the cell and the last space in the cell. Any help
woud be greatly appreciated.

 

[Mike H]

Hi,

Try this with your names in column A

This in B1 and drag down to extract the title
=LEFT(A1,FIND(" ",A1,1)-1)

This in C1 and drag down to extract the last name
=IF(ISERR(FIND(" ",A1)),A1,MID(A1,FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,1024))

This in D1 to extract the middle initial(s)
=TRIM(MID(A1,LEN(B1)+1,LEN(A1)-(LEN(B1)+LEN(C1))))


Mike

 

[dazoloko]

Thanks for help Mike it works a treat.

D

Hi,

Try this with your names in column A

This in B1 and drag down to extract the title
=LEFT(A1,FIND(" ",A1,1)-1)

This in C1 and drag down to extract the last name
=IF(ISERR(FIND(" ",A1)),A1,MID(A1,FIND("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,1024))

This in D1 to extract the middle initial(s)
=TRIM(MID(A1,LEN(B1)+1,LEN(A1)-(LEN(B1)+LEN(C1))))

Mike

I have a download into Excel which contains names, however the full name is
in one cell eg

[quoted text clipped - 8 lines]

between the first space in the cell and the last space in the cell. Any help
woud be greatly appreciated.