Formula wanted
 

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

Formula wanted
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

Formula wanted
 

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

Formula wanted
 

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

Formula wanted
 

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

Formula wanted
 

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

"Don Guillett" wrote:

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

Formula wanted
 

Don,
How could I paste the result into a cell using the code?
John

"Don Guillett" wrote:

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

Formula wanted
 

You must not have tried this because what I gave you DID paste the result in
the cell to the right.
c.Offset(, 1) = Replace(ms, " ", "")

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JohnUK" wrote in message
...
Don,
How could I paste the result into a cell using the code?
John

"Don Guillett" wrote:

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

Formula wanted
 

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


"Don Guillett" wrote:

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

Formula wanted
 

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


On Aug 2, 9:54 am, "David Biddulph" <groups [at] biddulph.org.uk
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