Extracting first letter from each word in a cell

mgm05267

Well-known Member
Joined
Nov 11, 2011
Messages
615
Hi All,
Am using the formula

Code:
[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....
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Maybe slightly simpler, but not much . . .
Code:
=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

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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.

Code:
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,357
Messages
5,486,380
Members
407,544
Latest member
mguevara

This Week's Hot Topics

Top