extract after multiple criteria

In one columns I have multiple variations. I need help with extracting numbers and decimal numbers after multiple criteria.
because there could be other numbers in the same cell the formula does not work.

Code:

---

LOOKUP(9.9E+307,--LEFT(MID(K27,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $K27&"1023456789")),999),ROW(INDIRECT("1:999"))))

---

Nor does this

Code:

---

=LOOKUP(10^8,--MID(N23,MIN(FIND({0,1,2,3,4,5,6,7,8,9},N23&"0123456789")),{1,2,3,4,5,6,7,8}))

---

Criteria
TRC
Rate is
RT
rate @

Example of what is in a cell:

Code:

---

ext 3333 at 12:00 on 23 July. Rate is 12.6677

---

I need 12.6677 extracted so I may use it for calculation.

I should be able to add to this as I get more examples.

Many thanks

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",10)),10))

will give you the last full word/number in the cell (if you are likely to have large words/numbers in the cell then up the two '10' s to above the maximum likely.

hello Paul. Thank you for your suggestion. It has worked for most of the samples. But not for all. A couple more examples

Code:

---

ext 883 at 17:00 on 27 June. Rate is 12.6677 (c.c. USD44.92)

---

and

Code:

---

ext 3333 at 12:00 on 23 July. ClientRate is 17.697 (no c.c.)

---

From the first example I require 12.6677 and second example I need to extract 17.697. May be if I can build up a criteria list?

I'd use a simple user defined function that keyed off of 'rate is'

Code:

---

Option Explicit


Function ExtractRate(s As String) As Double

    Dim v As Variant
    Dim i As Long
    ExtractRate = 0#
    v = Split(s, " ")
   
    For i = LBound(v) To UBound(v) - 2
        If InStr(1, v(i), "rate", vbTextCompare) > 0 And UCase(v(i + 1)) = "IS" Then
            ExtractRate = CDbl(v(i + 2))
            Exit Function
        End If
    Next

End Function


---

Sorry, mis-post!

@Paul_Hossler....I have multiple criteria, so this doesn't work i'm afraid.

How about

Code:

---

=IF(ISERR(FIND(" ",TRIM(MID(A1,SEARCH("Rate is ",A1)+8,255)))),TRIM(MID(A1,SEARCH("Rate is ",A1)+8,255)),LEFT(TRIM(MID(A1,SEARCH("Rate is ",A1)+8,255)),FIND(" ",TRIM(MID(A1,SEARCH("Rate is ",A1)+8,255)))-1))

---

Attachment 24927

Quote:

---

Originally Posted by Pamella

@Paul_Hossler....I have multiple criteria, so this doesn't work i'm afraid.

---

Worked for me on the 3 examples you gave


If you have other multiple criteria, can you tell us what they are?

Hi Pamella!

Code:

---

Sub test()
Dim s$, num#
s = "ext 3333 at 12:00 on 23 July. Rate is 12.6677"
's = "ext 883 at 17:00 on 27 June. Rate is 12.6677 (c.c. USD44.92)"  '-->for test
's = "ext 3333 at 12:00 on 23 July. ClientRate is 17.697 (no c.c.)"  '-->for test
num = Val(Split(s, "Rate is ")(1))
Debug.Print num
End Sub

---

--Okami

as per my post,

multiple Criteria-

TRC
Rate is
RT
rate @

OK - try this version

Code:

---

Option Explicit




Function ExtractRate(s As String) As Double
    Dim s1 As String
    Dim v As Variant
    Dim i As Long
   
    s1 = s
    s1 = Replace(s1, "TRC", "rate", , , vbTextCompare)
    s1 = Replace(s1, "@", "is", , , vbTextCompare)
    s1 = Replace(s1, "RT", "rate", , , vbTextCompare)
   
    ExtractRate = 0#
    v = Split(s1, " ")
   
    For i = LBound(v) To UBound(v) - 2
        If InStr(1, v(i), "rate", vbTextCompare) > 0 Then
            If UCase(v(i + 1)) = "IS" Then
                ExtractRate = CDbl(v(i + 2))
                Exit Function
           
            ElseIf IsNumeric(v(i + 1)) Then
                ExtractRate = CDbl(v(i + 1))
                Exit Function
            End If
        End If
    Next


End Function

---