Combine more than 2 text in a mid-find formula

[sujittalukde]

I am importing data from our accounting software and extracting certain data from one field called “Narration” by using the fllowing formula.

=MID(E2,FIND("to",E2,1)+3,(FIND("as",E2,1)-(FIND("to",E2,1)+3)))

Now in certain cases instead of “as”, two more words is used viz “towards” or “twds”

If the txt contains these two words, the above formula gives an error.
So I want to incorporate the same into one formula so that error does not come and the formula should give correct result whether the text contains “as”, “towards” or “twds”.

 

[Richard Schollar]

Hi

Try this:

=MID(E2,FIND("to",E2)+3,MIN(FIND({"as","towards","twds"},E2&"astowardstwds"))-FIND("to",E2)+3)

 

[sujittalukde]

Thanks for the reply. But one more thing is to be sdjusted.
In place of "to" two morw words to be added viz:
"to", "of", and "through"
Please incorporate these also.

 

[Richard Schollar]

=MID(E2,MIN(FIND({"to","of","through"},E2&"toofthrough"))+3,MIN(FIND({"as","towards","twds"},E2&"astowardstwds"))-MIN(FIND({"to","of","through"},E2&"toofthrough"))+3)

 

[sujittalukde]

Fine working and thanks for the solution.