First letter of each word

I have a column in Excel that has words in it like the following:

Employee ID Format
Company Options
Processing Schedule

Is there a function that would allow me to pull the first letter from
each word.
For example, Employee ID Format would give me EIF.
Company Options would give me CO, etc.
Thank you very much.

It is not elegant, but seems to work
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=0,LEFT(A1),IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=1,LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1),LEFT(A1,1)&MID(A1,FIND("
",A1)+1,1)&MID(A1,FIND(" ",SUBSTITUTE(A1," ","",1))+2,1)))
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Cardinal" <john.menken@ceridian.com> wrote in message
news:1152119003.555991.195000@j8g2000cwa.googlegroups.com...
>I have a column in Excel that has words in it like the following:
>
> Employee ID Format
> Company Options
> Processing Schedule
>
> Is there a function that would allow me to pull the first letter from
> each word.
> For example, Employee ID Format would give me EIF.
> Company Options would give me CO, etc.
> Thank you very much.
>

Bernard Liengme wrote...
>It is not elegant, but seems to work
>=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=0,
>LEFT(A1),IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,
>LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1),LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)
>&MID(A1,FIND(" ",SUBSTITUTE(A1," ","",1))+2,1)))
....

Could be shortened considerably to pick off only the first 3 words.

=LEFT(TRIM(A1),1)&MID(TRIM(A1),FIND(" ",TRIM(A1)&" ")+1,1)
&MID(TRIM(A1),FIND(" ",TRIM(A1)&" ",FIND(" ",TRIM(A1)&" ")+1)+1,1)

This could be extended to pick off the first 7 words.

=LEFT(TRIM(A4),1)&MID(TRIM(A4),FIND(" ",TRIM(A4)&" ")+1,1)
&MID(TRIM(A4),FIND(" ",TRIM(A4)&" ",FIND(" ",TRIM(A4)&" ")+1)+1,1)
&MID(TRIM(A4),FIND(" ",TRIM(A4)&" ",FIND(" ",TRIM(A4)&" ",
FIND(" ",TRIM(A4)&" ")+1)+1)+1,1)&MID(TRIM(A4),FIND(" ",TRIM(A4)&" ",
FIND(" ",TRIM(A4)&" ",FIND(" ",TRIM(A4)&" ",FIND(" ",
TRIM(A4)&" ")+1)+1)+1)+1,1)&MID(TRIM(A4),FIND(" ",TRIM(A4)&" ",
FIND(" ",TRIM(A4)&" ",FIND(" ",TRIM(A4)&" ",FIND(" ",TRIM(A4)&" ",
FIND(" ",TRIM(A4)&" ")+1)+1)+1)+1)+1,1)&MID(TRIM(A4),FIND(" ",
TRIM(A4)&" ",FIND(" ",TRIM(A4)&" ",FIND(" ",TRIM(A4)&"
",FIND(" ",
TRIM(A4)&" ",FIND(" ",TRIM(A4)&" ",FIND(" ",TRIM(A4)&"
")+1)+1)+1)+1)+1)+1,1)

Beyond 7 words, it'd be best to download and install Laurent Longre's
MOREFUNC.XLL add-in, available from http://xcell05.free.fr/english/ ,
and use it's REGEX.SUBSTITUTE function in formulas like

=REGEX.SUBSTITUTE(A1,"\B\S*\s*","")

On 5 Jul 2006 10:03:23 -0700, "Cardinal" <john.menken@ceridian.com> wrote:

>I have a column in Excel that has words in it like the following:
>
>Employee ID Format
>Company Options
>Processing Schedule
>
>Is there a function that would allow me to pull the first letter from
>each word.
>For example, Employee ID Format would give me EIF.
>Company Options would give me CO, etc.
>Thank you very much.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this **array-entered** formula:

=MCONCAT(REGEX.MID(A1,"\b\w",ROW(INDIRECT("1:"&REGEX.COUNT(A1,"\b\w")))))

To enter an array formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

If you want to use a VBA solution, <alt><F11> opens the VB Editor. Ensure your
project is highlighted in the Project Explorer window, then Insert/Module and
paste the code below into the window that opens.

To use it, enter the formula:

=FrstLtrs(A1)


================================
Option Explicit
Function FrstLtrs(str) As String
Dim ltr As String
Dim i As Long

FrstLtrs = Left(str, 1)

i = 1
Do Until InStr(i, str, " ") = 0
i = InStr(i, str, " ") + 1
FrstLtrs = FrstLtrs & Mid(str, i, 1)
Loop


End Function
================================

--ron