First letter of each word

  • Thread starter Thread starter Cardinal
  • Start date Start date
C

Cardinal

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 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)))
Click to expand...
....

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*","")
 
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.
Click to expand...

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
 
Back
Top