Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Axes, same scale wanted | Charts and Charting in Excel | |||
Bar chart help wanted | Charts and Charting in Excel | |||
formula wanted please, I have a list of data | Excel Discussion (Misc queries) | |||
offset example wanted | Excel Worksheet Functions | |||
Just wanted to say thanks! | Excel Discussion (Misc queries) |