Picking out 1st letter of names

[DianeandChipps]

I have been using the following formula to pick out the 1st letter of words.

=CONCATENATE(LEFT(A2,1),MID(A2,FIND(" ",A2)+1,1))

This has been working fine until there are 3 words in the cell.

I would like to be able to pick out the 1st letter of each word in the cell
no matter how many letters there are. Or to be able to pick out the 1st
letter of the first and last words only.

Words I am using are:
Project Manager
Ongoing Specialist Support
Monitoring and Evaluation

I would like the adjacent cells to show:
PM
OSS
ME

Is this possible without going down the route of coding?

Many thanks for your time.

 

[JE McGimpsey]

Note that your examples are inconsistent.

Ongoing Specialist Support ==> OSS
Monitoring and Evaluation ==> ME

FIrst and last is easy:

=LEFT(A2,1) & MID(A2, FIND("%",SUBSTITUTE(A2," ","%", LEN(A2) -
LEN(SUBSTITUTE(A2," ",""))))+1,1)

 

[Rick Rothstein]

=CONCATENATE(LEFT(A2,1),MID(A2,FIND(" ",A2)+1,1))

First off, for future reference, you don't need to use the CONCATENATE
function to concatenate text together... you can simply join the strings
together with ampersands. Also, if looking for the first character in a LEFT
(or RIGHT) function, you don't need to specify the character count... if you
leave out the 2nd argument, Excel automatically assumes 1 character. So
then, the above is identical to this...

=LEFT(A2)&MID(A2,FIND(" ",A2)+1,1)

Now, to answer your main question... you could probably construct a formula
to do what you want (up to a limit of 30 words I would guess), but the logic
will get messy if you have to skip over lower case leading letters (such as
your "and"). I may look at doing a formula later, but in the meantime, here
is a UDF (User Defined Function) which will do what you want for an
unlimited number of words...

Function FirstLetters(S As String) As String
Dim X As Long
Dim Letter As String
Dim Words() As String
Words = Split(S)
For X = 0 To UBound(Words)
Letter = Left(Words(X), 1)
If Letter = UCase(Letter) Then FirstLetters = FirstLetters & Letter
Next
End Function

If you are unfamiliar with how to install a UDF, it is quite simple. Press
Alt+F11 to go into the VB editor, click Insert/Module on its menu bar and
copy/paste the above UDF into the code window that appears. That is it. Go
back to your worksheet and enter this formula...

=FirstLetters(A2)

and copy it down as needed (you can copy it past your last data if desired).

 

[JE McGimpsey]

Two or three is easy too:

=LEFT(A2,1) & MID(A2,FIND(" ",A2)+1,1)&IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",
""))>1, MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1),"")

but it will produce "MaE", not "ME"

 

[Dick Kusleika]

I have been using the following formula to pick out the 1st letter of words.

=CONCATENATE(LEFT(A2,1),MID(A2,FIND(" ",A2)+1,1))

This has been working fine until there are 3 words in the cell.

I would like to be able to pick out the 1st letter of each word in the cell
no matter how many letters there are. Or to be able to pick out the 1st
letter of the first and last words only.

Is this possible without going down the route of coding?

I think *any* number of letters would be difficult without code. However,
you can brute force four letters with this formula

=LEFT(A1,1)&IF(NOT(ISERR(FIND(" ",A1,1))),MID(A1,FIND("
",A1,1)+1,1),"")&IF(NOT(ISERR(FIND(" ",A1,FIND(" ",A1,1)+1))),MID(A1,FIND("
",A1,FIND(" ",A1,1)+1)+1,1),"")&IF(NOT(ISERR(FIND(" ",A1,FIND(" ",A1,FIND("
",A1,FIND(" ",A1,1)+1)+1)))),MID(A1,FIND(" ",A1,FIND(" ",A1,FIND("
",A1,FIND(" ",A1,1)+1)+1))+1,1),"")

 

[DianeandChipps]

Many thanks to all that responded, sorry I haven't replied before now as I
have had sight problems.

All suggestions work picking out the first letter of each word.

I even managed Ricks UDF and it ignored the lower case 'and'.

Many thanks again

Diane