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
Medium Priority
381 Views
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
Question by:kgerb
• 8
• 4
• 3
• +2

LVL 24

Expert Comment

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

LVL 12

Author Comment

ID: 37006525
Yes
0

LVL 24

Expert Comment

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

LVL 12

Author Comment

ID: 37006548
Awesome, Thanks!
0

LVL 24

Accepted Solution

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

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

ID: 37006579
Cluskitt,
Great, thank you!
0

LVL 12

Author Comment

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

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

ID: 37006635
There he is:)

Thanks Barry, you're the man :)
0

LVL 24

Expert Comment

ID: 37006681
Barry - I think you deserve some points for that.
0

LVL 50

Assisted Solution

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

ID: 37006798
Here's another way.....

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

barry
0

LVL 12

Author Comment

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

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

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

0

LVL 12

Author Comment

ID: 37008077

Kyle
0

LVL 12

Author Closing Comment

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

kyle
0

## Featured Post

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â€¦
###### Suggested Courses
Course of the Month11 days, 13 hours left to enroll