# Extracting first letter from each word in a cell

#### mgm05267

##### Well-known Member
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

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### Gerald Higgins

##### Well-known Member
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
I'm not getting the same results with your formula,at least as posted.

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
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
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)

1,106,039
Messages
5,508,932
Members
408,702
Latest member
daz457

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...