Extracting first letter from each word in a cell

[mgm05267]

Hi All,
Am using the formula

[COLOR=#000000][FONT=lucida grande]=LEFT(LEFT(A1,FIND(" ",A1,1)),1)&LEFT(SUBSTITUTE(A1,LEFT(A1,FIND(" ",A1,1)),""),1)&LEFT(RIGHT(A1,FIND(" ",A1,1)-1),1)[/FONT][/COLOR]

to get first letter from each word in a cell..

If Cell A1 is "Excel Forum Questions", then in B1 am getting EFQ

Need to know any simpler formula to do this....

Also, if A1 is having variable words, like "Excel Questions", then it should give only EQ

Any suggestions please....

 

[Gerald Higgins]

Maybe slightly simpler, but not much . . .

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

Returns EQ as specified.

Note, the [A1&" "] sections are important for dealing with shorter text examples, and the number of spaces between the " characters gradually increments. Post back if this doesn't make sense.

 

[Asala42]

I'm not getting the same results with your formula,at least as posted.

How about a UDF?
The code below works for me in testing, it pulls the 1st letter of the string then any other characters that follow a space. It could be further tweaked to dissallow numbers & other non-letter characters from being pulled, but this works for your own examples.

Function PullFirstLetters(text) As String
'extract the first letters of each word in a sentence or string

mystring = Left(text, 1)

For i = 2 To Len(text) - 1
If Mid(text, i, 1) = " " Then
    mystring = mystring & Mid(text, i + 1, 1)
End If

Next i

PullFirstLetters = WorksheetFunction.Substitute(UCase(mystring), " ", "")
End Function

 

[Gerald Higgins]

I'm not getting the same results with your formula,at least as posted.

Did you copy and paste it ? Or type it in manually ? If manually, perhaps you missed some space characters. I just tried it again and it seems to work for up to 3 words, and can be adapted for more.

 

[Asala42]

Sorry - I was referring to the OP's formula.

(Your formula does work for me - at least up to 3 words which fall within the OP's examples)