# formula needed for initial

#### ericlch16

##### Active Member
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?

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Will anyone ever have a first name that is two words? ex: Mary Jo

That's:

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

Try:
Code:
``=LEFT(A1,1)&RIGHT(MID(A1,FIND(" ",A1),2),1)``

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.

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.

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.

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.

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

thanks

Replies
2
Views
533
Replies
8
Views
454
Replies
1
Views
121
Replies
1
Views
245
Replies
5
Views
113

1,206,758
Messages
6,074,769
Members
446,086
Latest member
daywi

### 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.

### Which adblocker are you using?

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

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