Formula wanted

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
One way
=LEFT(J2,1)&MID(J2,FIND(" ",J2)+1,1)&MID(J2,FIND(" ",J2,FIND(" ",J2)+1)+1,1)
 
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.
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top