Find all initials in Full Name...?

[question.maker]

i want an easy formula that can change name like this..

Ragavendra - R
Abdul Hasaad - A H
Mohamad Nasrin Sharma - M N S
Abdul Hamed Ibrahim Kaboor - A H I K


Please make very easy formula to do this excel operation...


Thanks in advance...

[arlu1201]

If your text is in A1, then in A2 type

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

[arlu1201]

Oops i just realised that though mine works for the 1st one, it doesnt for the 2nd example. Will work it out or someone with a better solution will come forth.

[Harribone]

Found this myslef not so long ago, only works for first three initials though. Still learning text manipulation so not sure how to modify this yet untill I fully understand how it all works.

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

EDIT: Beaten to it! Ignore this post

[Harribone]

=LEFT(A1,1)&IFERROR(" "&LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1),"")&IFERROR(" "&LEFT(RIGHT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),LEN(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))-FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))),1),"")&IFERROR(" "&LEFT(RIGHT(RIGHT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),LEN(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))-FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))),LEN(RIGHT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),LEN(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))-FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))))-FIND(" ",RIGHT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),LEN(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))-FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))))),1),"")

Extremely messy I know but this will work out up to 4 initials. It finds modifies the text by removing the left most name each time hence the length of the formula.

EDIT: Wrap this in UPPER to ensure result is in capitals (if you need this to be the case):

=UPPER("above formula")