One way
Sub getinitials()
For Each c In Range("j2:j5")
If Len(c) 0 Then
ms = ""
For i = 1 To Len(c)
If Mid(c, i, 1) = UCase(Mid(c, i, 1)) Then _
ms = ms & Mid(c, i, 1)
Next i
'MsgBox Replace(ms, " ", "")
c.Offset(, 1) = Replace(ms, " ", "")
End If
Next c
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnUK" wrote in message
...
Hi thank you both, but you are right, it does give me a problem if it is
more
or less than 3.
Is there another way to give a result regardless of the amount of words?
John

"David Biddulph" wrote:

But, as Don will have realised, that only deals with 3 words.
Gives only 3 letters if more than 3 words, and a #VALUE error if fewer
than
3.
--
David Biddulph

"Don Guillett" wrote in message
...
One way
=LEFT(J2,1)&MID(J2,FIND(" ",J2)+1,1)&MID(J2,FIND(" ",J2,FIND("
",J2)+1)+1,1)

"JohnUK" wrote in message
...
Hi, I am after a formula that can pick up the first letter of each
word
in a
cell. For example:
James Fred Bloggs - would become - JFB in the adjacent cell.
Is this possible?
Many thanks in advance
John