I have a list of words I need to get from a text string:
(just random words)
I have multiple cells with random words surrounding desired ones:
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!
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.