Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fisrt letter of every word
Is there any way I can concatenate the first letters of every word in a phrase?
eg management of ancient wood pasture Answer I am after in one cell "moawp" The lenhgth of the phrases would be variable, sya up to 15 words. I would value any help. Kind Regards Graham |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fisrt letter of every word
Graham,
I copied this from something written (I think) by Ron Rosenfield but if I've credited the wrong person then apologies to the original author. Put this UDF in a general module Function Firsts(str As String) As String part = Split(Trim(str)) For x = 0 To UBound(part) Firsts = Firsts & Left(part(x), 1) Next End Function Call with =Firsts(A1) where A1 is the string you want the first letters of. Mike "Graham H" wrote: Is there any way I can concatenate the first letters of every word in a phrase? eg management of ancient wood pasture Answer I am after in one cell "moawp" The lenhgth of the phrases would be variable, sya up to 15 words. I would value any help. Kind Regards Graham |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fisrt letter of every word
Mike,
You are a star! Many thanks for that and for replying so quickly. Graham Mike H wrote: Graham, I copied this from something written (I think) by Ron Rosenfield but if I've credited the wrong person then apologies to the original author. Put this UDF in a general module Function Firsts(str As String) As String part = Split(Trim(str)) For x = 0 To UBound(part) Firsts = Firsts & Left(part(x), 1) Next End Function Call with =Firsts(A1) where A1 is the string you want the first letters of. Mike "Graham H" wrote: Is there any way I can concatenate the first letters of every word in a phrase? eg management of ancient wood pasture Answer I am after in one cell "moawp" The lenhgth of the phrases would be variable, sya up to 15 words. I would value any help. Kind Regards Graham |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fisrt letter of every word
Your welcome, I merely reposted someone else's work
"Graham H" wrote: Mike, You are a star! Many thanks for that and for replying so quickly. Graham Mike H wrote: Graham, I copied this from something written (I think) by Ron Rosenfield but if I've credited the wrong person then apologies to the original author. Put this UDF in a general module Function Firsts(str As String) As String part = Split(Trim(str)) For x = 0 To UBound(part) Firsts = Firsts & Left(part(x), 1) Next End Function Call with =Firsts(A1) where A1 is the string you want the first letters of. Mike "Graham H" wrote: Is there any way I can concatenate the first letters of every word in a phrase? eg management of ancient wood pasture Answer I am after in one cell "moawp" The lenhgth of the phrases would be variable, sya up to 15 words. I would value any help. Kind Regards Graham |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fisrt letter of every word
I see you already have an answer, but I spent the time working this method
up, so I thought I would post it anyway (mainly for the archives)... Function FirstLetters(ByVal Str As String) As String Dim X As Long Str = " " & Str For X = Len(Str) To 2 Step -1 If Mid(Str, X - 1, 1) < " " Then Mid(Str, X, 1) = " " Next FirstLetters = Replace(Str, " ", "") End Function Rick "Graham H" wrote in message ... Is there any way I can concatenate the first letters of every word in a phrase? eg management of ancient wood pasture Answer I am after in one cell "moawp" The lenhgth of the phrases would be variable, sya up to 15 words. I would value any help. Kind Regards Graham |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fisrt letter of every word
Thanks for that Rick, your efforts are much appreciated.
Graham Rick Rothstein (MVP - VB) wrote: I see you already have an answer, but I spent the time working this method up, so I thought I would post it anyway (mainly for the archives)... Function FirstLetters(ByVal Str As String) As String Dim X As Long Str = " " & Str For X = Len(Str) To 2 Step -1 If Mid(Str, X - 1, 1) < " " Then Mid(Str, X, 1) = " " Next FirstLetters = Replace(Str, " ", "") End Function Rick "Graham H" wrote in message ... Is there any way I can concatenate the first letters of every word in a phrase? eg management of ancient wood pasture Answer I am after in one cell "moawp" The lenhgth of the phrases would be variable, sya up to 15 words. I would value any help. Kind Regards Graham |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
display one letter of a word in a cell | Excel Worksheet Functions | |||
Insert letter/no among word | Excel Discussion (Misc queries) | |||
Default Capital letter for 1st letter of a word | Excel Discussion (Misc queries) | |||
CAPITALIZONG FIRST LETTER OF EACH WORD IN A TITLE | Excel Worksheet Functions | |||
How do I get the first letter of a word in excel | Excel Discussion (Misc queries) |