Formula wanted

J

JohnUK

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
 
D

Don Guillett

One way
=LEFT(J2,1)&MID(J2,FIND(" ",J2)+1,1)&MID(J2,FIND(" ",J2,FIND(" ",J2)+1)+1,1)
 
D

David Biddulph

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

JohnUK

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
 
D

Don Guillett

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
 
J

JohnUK

Hi Don,
That actually worked very well and may use it in part of my programming if
there is really no way of putting in as a formula
John
 
D

Don Guillett

You must not have tried this because what I gave you DID paste the result in
the cell to the right.
If you want a formula try this UDF which must be put into a regular module
and then, if j2 has your text.
=gi(j2)

Function gi(x As Range)
If Len(x) > 0 Then
ms = ""
For i = 1 To Len(x)
If Mid(x, i, 1) = UCase(Mid(x, i, 1)) Then _
ms = ms & Mid(x, i, 1)
Next i
gi = Replace(ms, " ", "")
End If
End Function
 
J

JohnUK

Sorry Don,
Just realised it pasted into the cell adjacent. It was because I had the
column hidden (Gone daft as well as blind)
Many thanks
Regards
John
 
D

David Hilberg

Just as an exercise... If one knows the maximum number of initials one
might need, one can modify Don's formula thus:

=IF(LEN(J2)>0,LEFT(J2,1),"")
& IF(SUBSTITUTE(J2," ","~",1)=J2,"", MID(J2,FIND("~",SUBSTITUTE(J2,"
","~",1))+1,1))
& IF(SUBSTITUTE(J2," ","~",2)=J2,"", MID(J2,FIND("~",SUBSTITUTE(J2,"
","~",2))+1,1))
& IF(SUBSTITUTE(J2," ","~",3)=J2,"", MID(J2,FIND("~",SUBSTITUTE(J2,"
","~",3))+1,1))

The above covers 0 through 4 initials, and can of course be expanded
by adding more IFs with suitable incrementations in both SUBSTITUTE
functions. It assumes that the tilde ("~") is never present in one's
data.

- David
 
Top