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

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
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...
Top