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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
=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,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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