isolating initials from name in single cell

Duane

Board Regular
Joined
Mar 14, 2002
Messages
229
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
=LEFT(A1)&"."&MID(A1,FIND(" ",A1)+1,1)&"." produces "B.S." in cell B1

=LEFT(B1) produces "B"

=LEFT(RIGHT(B1,2)) produces "S"
 
Upvote 0
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?
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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".
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top