CODEWITHSUNDEEP
excel
brandon-estrella-dev
brandon-estrella-dev

Reputation: 377

isnumber, parse through sentence for variable word

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!!

just added - I realized that on my current computer I am runnig the starter edition so I do not have access to VBA. I found that using this formula worked good. =LOOKUP(1,RIGHT(TRIM(LEFT(J46,FIND("cats",J46)-1)),{1,2,3,4,5,6})). although it returns all numbers as positive.

my question now would be - how do you find a value that follows a constant word rather than precedes it? –

Upvotes: 1

Views: 416

Answers (3)

barry houdini
barry houdini

Reputation: 46341

This formula should get you the number of cats from text in A1

=LOOKUP(1000,MID(A1,FIND("cats",A1)-{1,2,3,4},{1,2,3,4})+0)

You can do the same for "gates". Note: FIND is case-sensitive so it will find "cats" but not "Cats" - change to SEARCH if you don't want case-sensitivity

Explanation: The MID part of the formula, i.e. this part

MID(A1,FIND("cats",A1)-{1,2,3,4},{1,2,3,4})

returns an "array" of 4 values, e.g. if A1 contains There were 3 cats that ran through 20 gates then that will return this "array". {" ","3 "," 3 ","e 3 "} then +0 converts those to numbers (or errors) so you get

=LOOKUP(1000,{#VALUE!,3,3,#VALUE!})

and where the lookup value is greater than any number in the lookup array (which it always will be here) then LOOKUP takes the last number, i.e. 3

You can cater for numbers up to 9999 by changing to

=LOOKUP(10000,MID(A1,FIND("cats",A1)-{1,2,3,4,5},{1,2,3,4,5})+0)

adjust in the same way for higher numbers

Upvotes: 3

mischab1
mischab1

Reputation: 1601

Here is a simpler function that will work as long as there is a space between the number and the word in front of it. 

Function FindValue(ByVal sString As String, ByVal sKey As String) As Integer
   Dim iFound As Integer

   iFound = InStr(LCase(sString), LCase(sKey))
   If iFound > 0 Then
      sString = Trim(Mid(sString, 1, iFound - 1))          ' discard the key and everything after it
      sString = Trim(Mid(sString, InStrRev(sString, " "))) ' discard everything before the number
   End If

   FindValue = Val(sString)

End Function

If you need it to work on values > 999 then use replace to get rid of any commas.

FindValue = Val(Replace(sString, ",", ""))

Upvotes: 0

dropper
dropper

Reputation: 73

Just put this in a Module and use the function in a new cell: =findPrevCount(A1,"cats")

Function findPrevCount(ByVal sString As String, ByVal sKey As String) As Long
    Dim v As Variant
    Dim i As Integer

    sString = Replace(sString, ".", "")   'strips periods
    sString = Replace(sString, ",", "")   'strips commas
    v = Split(sString, " ")               'turn sentence into several words

    For i = LBound(v) To UBound(v)
        'case insensitive search, can use wild cards
        If LCase(v(i)) Like LCase(sKey) And i > 1 Then
            If IsNumeric(v(i - 1)) Then
                findPrevCount = v(i - 1)
                Exit Function
            End If
        End If
    Next i

End Function

This should work with any text and any amounts. Just add logic if you want to make sure that one is greater than the other, using the =IF(findPrevCount(C2,"cat*") >= findPrevCount(C2, "gate*"), findPrevCount(C2, "cat*"), "Not enough cats.")

String                                          Cats    Gates   Result
There were 39 cats that ran through 25 gates.   39      25      39
There were 32 cats that ran through 40 gates.   32      40      Not enough cats.
There were 2 cats that ran through 1 gate.      2       1       2
There was 1 cat that ran through 1 gate.        1       1       1
There was 1 cat that ran through 2 gates.       1       2       Not enough cats.

Upvotes: 2

Related Questions