2
$\begingroup$

I have an excel file containing a long text in column A. I am looking for the words starting by "popul" such as popular and populate . I can find these cells by the formula:

    =SEARCH("popul",A1,1)

I want a function that returns the whole words starting by popul such as popular and populate.

$\endgroup$
  • 1
    $\begingroup$ Why don't you use Python? That would be so easy. If you want Python recipe I can answer. $\endgroup$ – Kasra Manshaei Jun 18 '15 at 12:12
  • $\begingroup$ Just let the search function be a conditional in an if...then statement. $\endgroup$ – Paul Jun 18 '15 at 23:30
  • $\begingroup$ I wrote a command to do it in bash but for exploration excel is something far better. $\endgroup$ – Hamideh Jun 19 '15 at 7:33
5
$\begingroup$

I'm no Excel expert, as I generally use Python or R instead, but this might get you started until an Excel expert comes along. In the meantime, it would help if you clarified your question. And you should be aware that search will only find you the index of the first match, not all matches in the string. If you only need the first hit, you can use

=MID(A1,SEARCH("popul",A1,1),IFERROR(FIND(" ",A1,SEARCH("popul",A1,1)),LEN(A1)+1)-SEARCH("popul",A1,1))

although I cannot claim this is the best way to do this. You really didn't specify where you want the results to appear, how they should look, or if you only have one cell you need to search in. It would also help to know the version of Excel you have. I'll also present a crude way to return all the hits in the string:

cells

Cell A1 contains the string, B1 has no formula, and if you run out of "n/a"s you can extend columns B, C, and D by filling down. The formulas are as follows:

B3 and below use

=IF(C2+1<LEN($A$1),C2+1,"n/a")

C2 and below use

=IFERROR(FIND(" ",$A$1,SEARCH("popul",$A$1,B2)),LEN($A$1)+1)

D2 and below use

=IFERROR(MID($A$1,SEARCH("popul",$A$1,B2),C2-SEARCH("popul",$A$1,B2)),"")

As you can see, there's little to no error checking except to deal with the match at the end of the string. In the end though, if you're going to use Excel for this you should probably create a user defined function or utilize VBA instead of in-cell formulas.

$\endgroup$
  • $\begingroup$ That is exactly what you wanna do. Cheers! $\endgroup$ – untitledprogrammer Jun 18 '15 at 21:07
  • $\begingroup$ It worked. Thanks. The first match would suffice for me because the text is short (an abstract of a paper) and a word with a specific root will most probably happen once in the text. $\endgroup$ – Hamideh Jun 19 '15 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.