extract after multiple criteria

[Pamella]

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.
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
=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:

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

[paulked]

=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.

[Pamella]

hello Paul. Thank you for your suggestion. It has worked for most of the samples. But not for all. A couple more examples
ext 883 at 17:00 on 27 June. Rate is 12.6677 (c.c. USD44.92) and
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?

[Paul_Hossler]

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




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

[paulked]

Sorry, mis-post!

[Pamella]

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

[paulked]

How about


=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))

24927

[Paul_Hossler]

@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?

[大灰狼1976]

Hi Pamella!

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

[Pamella]

as per my post,

multiple Criteria-

TRC
Rate is
RT
rate @

[Paul_Hossler]

OK - try this version



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