isolating initials from name in single cell

[Duane]

In A1 I have a name, "Bob Smith".
In B1, I'd like a formula that creates "B.S." I know it involves using MID, FIND, in some delicious mixture of commas and parentheses and concatenation.

I'd also like to get a handle on the formulas to put the "B" in C1 and the "S" in D1.

Thanks for the help.

[Mark W.]

=LEFT(A1)&"."&MID(A1,FIND(" ",A1)+1,1)&"." produces "B.S." in cell B1

=LEFT(B1) produces "B"

=LEFT(RIGHT(B1,2)) produces "S"

[Duane]

Splendid, thanks much!

[Aladin Akyurek]

On 2002-04-17 14:24, Duane wrote:
In A1 I have a name, "Bob Smith".
In B1, I'd like a formula that creates "B.S." I know it involves using MID, FIND, in some delicious mixture of commas and parentheses and concatenation.

I'd also like to get a handle on the formulas to put the "B" in C1 and the "S" in D1.

Thanks for the help.

Consist your data solely of

FirstName followed by a Space followed by Lastname?

[Duane]

Actually Mark (et al),

I'd like the b and the s (the first initial of first and last names) formula to be pulling from the A1 cell that has the full name in it, not from the B1 (so I can use either method).

And Aladin, yes, the format in A1 will always be FirstName LastName.

But, were you preparing to propose formulas incase the name in A1 was not in that format?
Please do!

Thanks again...

[Aladin Akyurek]

On 2002-04-17 18:39, Duane wrote:
Actually Mark (et al),

I'd like the b and the s (the first initial of first and last names) formula to be pulling from the A1 cell that has the full name in it, not from the B1 (so I can use either method).

And Aladin, yes, the format in A1 will always be FirstName LastName.

But, were you preparing to propose formulas incase the name in A1 was not in that format?
Please do!

Thanks again...

In B1 enter:

=LEFT(A1)&"."

In C1 enter:

=MID(A1,SEARCH(" ",A1)+1,1)&"."

Instead of posting the forgoing, I replied with that question you quoted above:

In D1 enter:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2,MID(A1,SEARCH("@",SUBSTITUTE(A1," ","@",2))+1,1)&".","")

You need more to capture the initials of some e.g., "catholic" names.

Names including titles, particles like Jr., etc. go untreated. And, some names are not covered at all, e.g.:

Marie-France Tardieu becomes from above M. and T.;
P. N. Johnson-Laird --> P. N. J.
Paul van Doorn --> P. v. D.

Aladin

[Duane]

Thanks much!!

[Mark W.]

On 2002-04-17 18:39, Duane wrote:
Actually Mark (et al),

I'd like the b and the s (the first initial of first and last names) formula to be pulling from the A1 cell that has the full name in it, not from the B1 (so I can use either method).

Too easy...

If A1 contains "Bob Smith" you can select B1:C1 and enter the following array formula...

{=MID(A1,{0,1}*FIND(" ",A1)+1,1)}

... to return "B" in cell B1 and "S" in cell C1.

Note: As mentioned this is an array formula which must be entered using the Control+Shift+Enter key combination. For more on array formulas see the Excel Help Index topic for "About array formulas and how to enter them".

[Duane]

Hey Mark,

Thanks for this. Yeah, array formulas. MrExcel calls them "CSE" formulas to remind us to use control-shift-enter when entering them.

Good show, mate!