How to pluck out initials of a 2 or 3 word name

C

Craig Brody

For example, in A1 might be the name:

John Smith or John L. Smith or John Lee Smith

I would like the result in cell B1 to be JS only

Can a function be used? I have come up so far with
"=MID(A1,1,1)&MID(A1,FIND(" ",A1)+1,1)" but it gives me the middle name
initial and leaves out the last name initial

any help much appreciated!!
thank you
 
B

Bernie Deitrick

Craig,

=LEFT(A1,1) & MID(A1,IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,FIND(" ",A1)+1,FIND(" ",A1,FIND("
",A1)+1)+1),1)

All on one line.

HTH,
Bernie
MS Excel MVP
 
Top