Consulting

Results 1 to 11 of 11

Thread: extract after multiple criteria

  1. #1
    VBAX Regular
    Joined
    Jul 2019
    Posts
    36
    Location

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

  2. #2
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    =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.
    Semper in excretia sumus; solum profundum variat.

  3. #3
    VBAX Regular
    Joined
    Jul 2019
    Posts
    36
    Location
    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?

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Sorry, mis-post!
    Semper in excretia sumus; solum profundum variat.

  6. #6
    VBAX Regular
    Joined
    Jul 2019
    Posts
    36
    Location
    @Paul_Hossler....I have multiple criteria, so this doesn't work i'm afraid.

  7. #7
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    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))
    Book2.xlsx
    Semper in excretia sumus; solum profundum variat.

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by Pamella View Post
    @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?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    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

  10. #10
    VBAX Regular
    Joined
    Jul 2019
    Posts
    36
    Location
    as per my post,

    multiple Criteria-

    TRC
    Rate is
    RT
    rate @


  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •