1

I have a list of words I need to get from a text string:

  • A cow
  • Dog-boy
  • Hello-get it

(just random words)

I have multiple cells with random words surrounding desired ones:

  • Random wordlsads A cow askjdhakj
  • sla;alsj Dog-Boy l;sasla
  • skla Hello-get it ksalksajsa

And I need an excel function to extract my specified words from this cell. The problem is that I need 1 universal formula that checks all words in a string and returns if something matches one of the words in a list.

I would use following function to extract one word from 1 cell:

=TRIM(MID(D4,SEARCH(R3,D4),LEN(R3)))

Where D4 is a text string I need to check and R3 is a word I am looking for.

I was trying to combine those using IFS formula:

=TRIM(IFS($R$4=MID(D2,SEARCH($R$4,D2),LEN($R$4)),$R$4,$R$2=MID(D2,SEARCH($R$2,D2),LEN($R$2)),$R$2))

But, as some of you already get, it returns #VALUE! error, because when first SEARCH formula runs and does not find any match it returns #VALUE! error and whole function returns it, despite the fact that it could actually have 1 TRUE result.

I googled a lot and, as I understand, I need to use TRANSPOSE formula to create an array, and somehow get result from there, but, I guess, I am not that smart yet.

Would appreciate some help on this one. Thank you!

Sergei Frolov
  • 15
  • 2
  • Be careful here, substrings containing a word on your list may get included returning false positives. E.g. Jack in Jacky...Etc etc. I would recommend using Regular Expressions and build an UDF to return all found words from your list. – JvdV Jan 07 '20 at 18:41

2 Answers2

0

Use INDEX/AGGREGATE:

=INDEX(A:A,AGGREGATE(15,7,ROW($A$1:$A$3)/(ISNUMBER(SEARCH($A$1:$A$3,C1))),1))

enter image description here

for you:

=INDEX(R:R,AGGREGATE(15,7,ROW($R$2:$R$4)/(ISNUMBER(SEARCH($R$2:$R$4,D4))),1))
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Wow, that was fast. Is it that easy and I am just a dumbass? :D Anyway, could you please clarify what does "15,7" numbers stand for. Where did you get those? – Sergei Frolov Jan 07 '20 at 17:48
  • `15` returns the first match and `7` ignores errors. @SergeiFrolov – Scott Craner Jan 07 '20 at 17:51
  • Again, that is up to the OP to declare that they are in need of that exactness. I just answer the question as it is. @JvdV – Scott Craner Jan 07 '20 at 19:24
  • @JvdV Yeah, I can see that. In fact, there won't be any other words in text strings, so these 2 will do the work I need properly. RegEx is not an option, as it's macOS excel I have on work and VBA is reeeeeeally slow on macOS excel when it comes to big data. But thank you for an advise as well! – Sergei Frolov Jan 07 '20 at 19:32
0

If Column A Houses Cells with random words with desired ones and Column C lists the desired ones then you can use:

=LOOKUP(2^15,SEARCH($C$1:$C$3,A1,1),$C$1:$C$3)

And copy down. Adjust range $C$1:$C$3 to suit your actual range.

Edit Explanation as below.

I do not know origins of this formula. I came across this construct for the first time on MrExcel where "Aladin Akyurek" posted it.

The 2^15 part calculates to 32768 which is more (just by one number) than the maximum number of characters an Excel cell can hold theoretically i.e. 32767. It was posted somewhere by a user named "XOR LX" and I have used it since.

The construct uses LOOKUP function's ability to deal with Arrays and Errors enabling a fairly straightforward solution.

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27