formula needed for initial

[ericlch16]

I have these in column A

John Smith
Raphael Van Der Beek
Catherine Zeta Jones
Bob Thomas


I want a formula to return the initial of the names so that in column B it is

JS
RV
CZ
BT

any help?

 

[mathchick]

Will anyone ever have a first name that is two words? ex: Mary Jo

 

[Andrew Poulsom]

That's:

=LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)

 

[JackDanIce]

Try:

=LEFT(A1,1)&RIGHT(MID(A1,FIND(" ",A1),2),1)

 

[Rick Rothstein]

This will do what you asked for...

=LEFT(A1)&MID(A1,FIND(" ",A1&" ")+1,1)

but mathchick raises a good question. If you have any double first names, then you will not get the initial for the last name, rather, you will get the initials for both first names. For example... Mary Jo Van Der Beek will come out MJ. With your names all in the same cell, there is no way to work around this unless you have a list of ALL the possible double first names in the world.

 

[mathchick]

there is no way to work around this unless you have a list of ALL the possible double first names in the world.

assuming of course there's never any overlap between last part of first name and the first part of a last name But now I'm just being argumentative.

 

[Rick Rothstein]

assuming of course there's never any overlap between last part of first name and the first part of a last name But now I'm just being argumentative.

You are thinking of something like Mary Jo Smith and Mary Josephs, right? If so, incorporating the space that has to follow the first name in the search of double first names should eliminate that problem I would think.

 

[mathchick]

I was being super snarky and thinking of Raphael Van -- Der Beek and Raphael -- Van Hasset. If Raphael Van were listed as a possible first name then Raphael Van Hasset would return the initials RH even if it was actually RV. But again, the likelihood of this ever even being a possible issue is quite slim, just being a nuisance. It's one of those days.

 

[ericlch16]

Thanks for everybody on MrExcel for great/quick suggestions. any above formula above will do for me.

thanks