Displaying First Chars of Words in a Cell?

[joezeppy]

Hi,

I have a column of words (company names) and I'd like to display the first character of each word in the name in the next column. The idea is to evaluate and compare the initials made up in each of these names.

So the idea is to take column A and produce column B as below:

ALMOND BOARD OF CALIFORNIA ABOC
ALTRIA GROUP AG
AMERICAN AIRLINES AA
AMERICAN ARBITRATION ASSOCIATION AAA
AMERICAN EXPRESS COMPANY AEC
AMERICAN FAMILY INSURANCE AFI
AMERICAN HONDA MOTOR CO AHMC
AMERICAN LICORICE AL
AMERIPRISE FINANCIAL AFI
AMTRAK NATIONAL RAILROAD PASSENGER CORP ANRP

Any easy formula solution? Thanks in advance.

 

[Gerald Higgins]

Why don't you want ANRPC for the last one ?
Why do you want AFI for Ameriprise Financial, and not AF ?

 

[joezeppy]

Why don't you want ANRPC for the last one ?
Why do you want AFI for Ameriprise Financial, and not AF ?

Sorry, those were just typos. I did it manually.

 

[Walshie1987]

Here's one possible solution: (modified from another forum)

=LEFT(A1)&MID(A1,FIND(" ",A1&"  ")+1,1)&MID(A1,FIND("^^",SUBSTITUTE(A1,"  ","^^",2)&"^^")+1,1)&MID(A1,FIND("^^",SUBSTITUTE(A1,"  ","^^",3)&"^^")+1,1)

This will do three words, if you need a fourth add:

&MID(A1,FIND("^^",SUBSTITUTE(A1,"  ","^^",4)&"^^")+1,1)

Where A1 is the target cell.

Cheers
Chris