Extract Numbers after a specific word in a cell/string, to different cells

Question

I want to extract the price/rate from the below string.

"38A Standard - HotelCollect 2019-01-01 Per Day Rate CAD 99.00 2019-01-02 Per Day Rate CAD 99.00"

I want to extract the rate that appears right after "CAD" to different cells or if possible add them as well.

I am really stuck with this can someone help.

Answer 1

1: Find how many rates you need to get

=(LEN(A1)-LEN(SUBSTITUTE(A1,"CAD","")))/3

Copy this formula into B1. It compares the length of your string to the length of your string after all instances of "CAD" are removed, and divides the result by 3 to find out how many times "CAD" appears.

2: Seed the next function

1

Write a 1 into C1. This isn't strictly necessary but we'll do it so that the formula we write in D1 can be copied across without modification.

3: FIND each instance

=IF($B1>COLUMN()-4,FIND("CAD",$A1,C1+1),"")

This formula copies across for as many instances of the desired string as there might be in your data. Note the hard coded column value offset that compares this cell's location to the location of the counter in step 1. If you arrange your data differently, you'll have to adjust this formula (once).

Write this formula into D1; for this example I've copied across from D1:G1.

4: Do the MID

=IFERROR(IF(D1>0,TRIM(MID($A1,D1+4,6)),""),"")

This formula copies across for as many instances.. etc. It does require you to manually copy across as many times as you did in step 3.

Write this formula into H1; for this example I've copied across from H1:K1.

NOTES

1. It's pretty sloppy to set something like this up with so many helper columns and this much manual effort, but your question didn't say anything about your use case or workflow so I didn't put a ton of time into thinking about ways to streamline. If all your data is in column A, copying this setup exactly as presented here will give you the data you need in columns H:K, and copy down for as much data as you have in A.
2. If your actual use case is looking for a SUM or whatever, that part is trivial of course and in this example would go in column L; everything from B:K can be hidden if you like, or certainly could all be combined in a 'god formula' though the effort required to do so might not be indicated depending on your workflow limitations.
3. There's some fudging in step 4; I've written it to get the 6 characters that follow the space that follows "CAD" (and then trim any spaces); this assumes the size of the output you want never exceeds 6 characters. Again, you didn't include much sample data or any description in your spec so I'm guessing a bit here as to what the parameters of possible data are going to look like; this works when the string you want follows the format dd.cc or ddd.cc exactly - omitting digits will cause problems, as will rates exceeding 999.99 CAD (for the record, difficulties like this are what people mean when they ask questions to be specific and include sample data input and output).

Answer 2

Try the following user defined function:

Public Function PriceFinder(s As String) As Double
    Dim i As Long
    arr = Split(s, " ")
    For i = LBound(arr) To UBound(arr)
        If arr(i) = "CAD" Then
            PriceFinder = CDbl(arr(i + 1))
            Exit Function
        End If
    Next i
End Function

EDIT#1:

To get more than one price, use this version of the UDF:

Public Function PriceFinder(s As String) As String
    Dim i As Long
    arr = Split(s, " ")

    For i = LBound(arr) To UBound(arr)
        If arr(i) = "CAD" Then
            If PriceFinder = "" Then
                PriceFinder = arr(i + 1)
            Else
                PriceFinder = PriceFinder & "," & arr(i + 1)
            End If
        End If
    Next i

    If Right(PriceFinder, 1) = """" Then PriceFinder = Left(PriceFinder, Len(PriceFinder) - 1)

End Function