Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Extract First "n" Words From Sentence

Posted on 2011-10-21
17
Medium Priority
?
381 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:kgerb
  • 8
  • 4
  • 3
  • +2
17 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 37006518
And words are defined by boundaries consisting of a space?
0
 
LVL 12

Author Comment

by:kgerb
ID: 37006525
Yes
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37006533
I think this does what you want:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))<=$D$2,A2,LEFT(A2,FIND("#",SUBSTITUTE(A2," ","#",$D$2))))
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Author Comment

by:kgerb
ID: 37006548
Awesome, Thanks!
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 400 total points
ID: 37006555
Actually, should be this I think:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))<=$D$2-1,A2,LEFT(A2,FIND("#",SUBSTITUTE(A2," ","#",$D$2))))
0
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 400 total points
ID: 37006566
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
 
LVL 12

Author Comment

by:kgerb
ID: 37006579
Cluskitt,
Great, thank you!
0
 
LVL 12

Author Comment

by:kgerb
ID: 37006592
Where is Barry at?  I need something crazy like what he did yesterday with the Lookup() and Row() and Indirect().

:)
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 800 total points
ID: 37006623
>Where is Barry at?

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

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

regards, barry
0
 
LVL 12

Author Comment

by:kgerb
ID: 37006635
There he is:)

Thanks Barry, you're the man :)
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 37006681
Barry - I think you deserve some points for that.
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 800 total points
ID: 37006722
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 37006798
Here's another way.....

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

barry
0
 
LVL 12

Author Comment

by:kgerb
ID: 37006870
@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
 
LVL 81

Assisted Solution

by:byundt
byundt earned 400 total points
ID: 37008028
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
 
LVL 12

Author Comment

by:kgerb
ID: 37008077
Thanks Brad, good one!!!

Kyle
0
 
LVL 12

Author Closing Comment

by:kgerb
ID: 37008112
Thank you all very much.  Excellent answers, as usual.  I appreciate your help.

kyle
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question