isnumber to parse through words in a sentence

I am currently using isnumber to determine whether a specific word exist within a cell that contains a sentence. I am trying to determine how to find the value of a number that always precedes, or follows, a specific word. How can I manipulate the formula, or create a series of formulas, to pull a 'variable word' out that precedes, or follows, a 'constant word'?

for example,

There were 39 cats that ran through 45 gates.
There were 32 cats that ran through 20 gates.

I am looking to pull the number of cats that ran through gates.


My current progress...

The number of cats will always fall between 1 - 100, and the number of gates will also always fall between 1 - 100.
Currently, I am running 100 columns. Each column has a formula like =ISNUMBER(SEARCH(" 2 cats ",'cell that contains the sentence')), the next column would be =ISNUMBER(SEARCH(" 3 cats ",'cell that contains the sentence')). This returns true or false so that I can have a column that finds the true throughout the rows and returns the cat value of that cell...

my current problem...
There are two numbers within each sentence so...I currently have two true columns.


Question...
is there a way to pull out the number that precedes the word cats?
the number of cats will never always be greater than the number of gates nor always less than the number of gates.
the word 'cats' will remain constant throughout each sentence but the number will change...


hopefully this is clear enough and someone can help! Thanks in advance!!

bestrella,

Welcome to the Excel Forum.


If cell A1 contains:
There were 39 cats that ran through 45 gates.

The formula in cell B1:
=-LOOKUP(1,-RIGHT(TRIM(LEFT(A1,FIND("cats",A1)-1)),{1,2,3,4,5,6}))
will display 39

The formula in cell C1:
=-LOOKUP(1,-RIGHT(TRIM(LEFT(A1,FIND("gates",A1)-1)),{1,2,3,4,5,6}))
will display 45

bestrella,

how would I modify that formula to find the variable that follows the constant rather than precedes it?

I do not know. But, the answer would be one for my archives.

In the Reply to Thread box, enter just put the word BUMP then click on the Post Quick Reply button, and someone else will assist you.

Or, create a new Post with another title, say, Find the text/word that immediatley follows a number in a text string.

bestrella,

Thanks. This is one for my archives.