Formula to return initials of a name.

[Swatb91]

Does any one know how to return the initials of a name.

The names are in the format Last, First, Middle Initial.

So

John William Smith would be entered as Smith, John, W

I am looking for a formula such as the one below because it works even if the person leaves out the middle inital or only puts the first name or the last name its kind of a fail safe however I cannot figure out how to manipulate the foumula for the format of the names. If returns the initials as SJW instead of JWS

=if(len(B3)-len(SUBSTITUTE(B3," ",""))=0,left(B3,1),if(len(B3)-len(SUBSTITUTE(B3," ",""))=1,left(B3,1)&mid(B3,find(" ",B3)+1,1),left(B3,1)&mid(B3,find(" ",B3)+1,1)&mid(B3,find(" ",B3,find(" ",B3)+1)+1,1)))

I have also tried this one but cannont get it to work with the Last, First, Middle Initial format.

=IF(ISERROR(FIND(" ",A1)),LEFT(A1,1),TRIM(LEFT(A1,1)&MID(A1,FIND(" ",A1&" ")+1,1)&MID(A1,FIND(" ",SUBSTITUTE(A1&" "," ","~",1))+1,1)))

I was able to make the one below work with the format I need however it is laking that fail safe in that if the person leaves out there middle inital it gives an error.

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

Sorry I'm kind of an amature when it comes to formulas if anyone has a soultion it would be greatly appreciated. Thanks

 

[mikerickson]

How about
=UPPER(LEFT(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",255),1),255,255)),1) & LEFT(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",255),2),255,255)),1) & LEFT(A1,1))

 

[barry houdini]

Hello swatb91, welcome to MrExcel,

Another option might be to use CHOOSE function against the number of spaces in the text, e.g.

=CHOOSE(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1,LEFT(B3),MID(B3,FIND(" ",B3)+1,1)&LEFT(B3),MID(B3,FIND(" ",B3)+1,1)&RIGHT(B3)&LEFT(B3))

 

[Swatb91]

How about
=UPPER(LEFT(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",255),1),255,255)),1) & LEFT(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",255),2),255,255)),1) & LEFT(A1,1))

Thanks, thats defenantly better than what I have so far.

The only thing I noticed compaired to the first one is if someone by mistake enters the name without commas it doesn't calculate correctly.

Do you know a way to account for either way they enter the name?

By the way...Do you do the Dueling Podcasts?

 

[Swatb91]

Hello swatb91, welcome to MrExcel,

Another option might be to use CHOOSE function against the number of spaces in the text, e.g.

=CHOOSE(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1,LEFT(B3),MID(B3,FIND(" ",B3)+1,1)&LEFT(B3),MID(B3,FIND(" ",B3)+1,1)&RIGHT(B3)&LEFT(B3))

This one is almost exaclty what I need. The only thing I would change is take out the &RIGHT funtion at the end and change it to some sort of &MID function to prevent a period or comma from being displayed if a user insterts either of those after there middle intial.

Sorry for being so picky I should of menthioned those things in my original post.

 

[mikerickson]

(What's a podcast and how do they duel? I do like to watch various battling robot shows on TV.)

Names and their formats are a big pain.
Rather than trying to use an algorithm to decide if an entry in the wrong format, one could use Data Validation to insure (or strongly suggest) that the user enters 2 commas when they enter a name.

 

[Swatb91]

The format it should be in is already listed, its just that some people have no attention to detail what-so-ever.

Is there acually data valadation other than an input message to ensure commas are before the spaces.

Or, is it possible to custom format a cell as "text, text, text" kind of like you would format a date cell.

 

[barry houdini]

The only thing I would change is take out the &RIGHT funtion at the end and change it to some sort of &MID function.....

OK, that would look like this, I think

=CHOOSE(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1,LEFT(B3),MID(B3,FIND(" ",B3)+1,1)&LEFT(B3),MID(B3,FIND(" ",B3)+1,1)&MID(B3,FIND(" ",B3,FIND(" ",B3)+1)+1,1)&LEFT(B3))

 

[mikerickson]

"commas are before the spaces."
The Validation formula =(LEN(A1)-LEN(SUBSTITUTE(A1,", ","")))=2*(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))) will do that.

But, do you want commas in front of all the spaces? Van Damme, Jean, Claude

The Validation formula =(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))<=2) will insure that there are no more than two , in the entered value.

Setting Validation's alert style to other than STOP will alter the user, but not force them to add , if it isn't needed.

 

[Swatb91]

Thank you all for the help, This is exactly what i needed, much appreciated.