First letter of each word

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cardinal - 05 Jul 2006 18:03 GMT

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.

Reply to this Message

Bernard Liengme - 05 Jul 2006 18:29 GMT

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

Signature

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

>I have a column in Excel that has words in it like the following:
>
[quoted text clipped - 7 lines]
> Company Options would give me CO, etc.
> Thank you very much.

Reply to this Message

Harlan Grove - 05 Jul 2006 19:39 GMT

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*","")

Reply to this Message

Ron Rosenfeld - 05 Jul 2006 19:48 GMT

>I have a column in Excel that has words in it like the following:
>
[quoted text clipped - 7 lines]
>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

Reply to this Message