Extract First "n" Words From Sentence

Ok, I'm doing a quiz of the week for my work.  This weeks quiz is to extract the first "n" words from a sentence.  I need as many different ways to do this as possible.  Any ideas would be appreciated.  Thanks

Kyle
Quiz-01.xlsx
LVL 12
kgerbChief EngineerAsked:
Who is Participating?
 
StephenJRCommented:
Actually, should be this I think:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))<=$D$2-1,A2,LEFT(A2,FIND("#",SUBSTITUTE(A2," ","#",$D$2))))
0
 
StephenJRCommented:
And words are defined by boundaries consisting of a space?
0
 
kgerbChief EngineerAuthor Commented:
Yes
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
StephenJRCommented:
I think this does what you want:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))<=$D$2,A2,LEFT(A2,FIND("#",SUBSTITUTE(A2," ","#",$D$2))))
0
 
kgerbChief EngineerAuthor Commented:
Awesome, Thanks!
0
 
CluskittCommented:
Assuming sentence is in A1 and n in A2:

You can use VBA:
Dim strSplit As Variant
strSplit = Split(Range("A1").Value, " ")
For a = 0 To Range("A2").Value - 1
  Cells(a + 2, 1) = strSplit(a)
Next

Another way (without splitting)
Dim strVar As String
strVar = Range("A1").Value
For a = 0 To Range("A2").Value -
  Cells(a + 2, 1) = LEFT(strVar, InStr(strVar, " ") - 1)
  strVar = Mid(strVar, InStr(strVar, " ") + 1, Len(strVar))
Next
0
 
kgerbChief EngineerAuthor Commented:
Cluskitt,
Great, thank you!
0
 
kgerbChief EngineerAuthor Commented:
Where is Barry at?  I need something crazy like what he did yesterday with the Lookup() and Row() and Indirect().

:)
0
 
barry houdiniCommented:
>Where is Barry at?

To shorten Stephen's a little.....(Not for points)

=LEFT(A2,FIND("#",SUBSTITUTE(A2&REPT(" ",$D2)," ","#",$D2)))

regards, barry
0
 
kgerbChief EngineerAuthor Commented:
There he is:)

Thanks Barry, you're the man :)
0
 
StephenJRCommented:
Barry - I think you deserve some points for that.
0
 
barry houdiniCommented:
No, actually, I'm losing it - could be just this

=LEFT(A2,FIND("#",SUBSTITUTE(A2&"#"," ","#",$D2)))

...but yours was the original working method. I like the shorter formulas but arguably using an IF is more efficient as the irrelevant TRUE/FALSE part is not evaulated....so it could still be more efficient overall

regards, barry
0
 
barry houdiniCommented:
Here's another way.....

=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)*D$2))

barry
0
 
kgerbChief EngineerAuthor Commented:
@Barry,
Ahhh, yes, I saw you use that one a few days ago as well...nice!

hey, can you use this somehow?

=REPLACE(A1,1,LOOKUP(2^15,FIND("/",A1,ROW(INDIRECT("1:"&LEN(A1))))),"")

You used it yesterday in this thread:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27407639.html?cid=1573#a37003343

Honestly I haven't figured it out enough to know how to modify it for this application (even with your explanation).

Kyle
0
 
byundtCommented:
Here is a user-defined function using Split and Join. You would put it in a regular module sheet and use it with a worksheet formula like:
=FirstN(A2,D$2)
Function FirstN(sString As String, nWords As Long) As String
Dim v As Variant
v = Split(sString, " ", nWords + 1)
If UBound(v) >= nWords Then v(nWords) = ""
FirstN = Trim(Join(v, " "))
End Function

Open in new window


If the number of words in the sentence is less than the value in D2, then the original sentence is returned.

Brad
0
 
kgerbChief EngineerAuthor Commented:
Thanks Brad, good one!!!

Kyle
0
 
kgerbChief EngineerAuthor Commented:
Thank you all very much.  Excellent answers, as usual.  I appreciate your help.

kyle
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.