Extract Dollar value from a text String

[Kyosti]

Hello all,
It has been awhile since I have asked for help, but I need a quick hand. I need to extract a dollar value from the text below.


“I spoke with F on phone number f regarding the status of policy number f. I advised that current amount due of $10. includes a past due amount of $10. that is due before 8/14 to avoid cancellation of the policy. To prevent a loss of coverage, F made a payment of $20.0 with (CPay). The reference number for the payment made is (reference).”


I need to be able to pull the different dollar values based on the text that precedes them. For example I need one for the “amount due”, another for the “Past due” and the last for the “Payment of”. This way I can extract the three different values into 3 different cells. The verbiage is the criteria because it will always be the same.


I tried searching for something that would help and I found this but it will only return the first value found, and I cannot figure out how to adjust it.


=MID(B4,FIND("$",B4),FIND(" ",B4&" ",FIND("$",B4))-FIND("$",B4))+0


Thanks again in advance.

 

[AhoyNC]

Actual there was a change in your text. In your first example you had the text "with (" after the last amount. In your second example it's just "with".
The formula from jtakw is probably the better way to go, but my formula for the payment could be changed to:

Excel Workbook
	B	C	D
4	I spoke with Customer Name on phone number 555-555-5555 regarding the status of policy number 123456789. I advised that current amount due of $28.03 includes a past due amount of $14.02 that is due before 8/16/2018 to avoid cancellation of the policy. To prevent a loss of coverage, Customer Name made a payment of $104.11 with a Visa. The reference number for the payment made is 123456789.
5
6
7		Amount Due	28.03
8		Past Due	14.02
9		Payment	104.11
Sheet

 

[mse330]

This is one more approach using UDF (User Defind Function) if interested ...

Function Extract_Amounts(Txt As String, Seq As Integer) As Double
Dim Arr() As Variant, Ref As String, Cnt As Integer, ArCnt As Integer
Cnt = Len(Txt) - Len(Replace(Txt, "$", ""))
ReDim Arr(1 To Cnt)
For x = 1 To Len(Txt)
    If Mid(Txt, x, 1) = "$" Then
        Cnt = x + 1
        Do While Mid(Txt, Cnt, 1) <> " "
            Ref = Ref & Mid(Txt, Cnt, 1)
            Cnt = Cnt + 1
        Loop
            ArCnt = ArCnt + 1
            Arr(ArCnt) = Ref
            Ref = ""
    End If
Next x
Extract_Amounts = Arr(Seq)
End Function

The UDF could be used as follows

Book1
	A	B	C	D
1	Text Phrase	Amount 1	Amount 2	Amount 3
2	I spoke with F on phone number f regarding the status of policy number f. I advised that current amount due of $10. includes a past due amount of $10. that is due before 8/14 to avoid cancellation of the policy. To prevent a loss of coverage, F made a payment of $20.0 with (CPay). The reference number for the payment made is (reference).	10	10	20
3	I spoke with Customer Name on phone number 555-555-5555 regarding the status of policy number 123456789. I advised that current amount due of $28.03 includes a past due amount of $14.02 that is due before 8/16/2018 to avoid cancellation of the policy. To prevent a loss of coverage, Customer Name made a payment of $104.11 with a Visa. The reference number for the payment made is 123456789.	28.03	14.02	104.11
Sheet1

Cell Formulas
Range	Formula
B2	=Extract_Amounts(A2,1)
B3	=Extract_Amounts(A3,1)
C2	=Extract_Amounts(A2,2)
C3	=Extract_Amounts(A3,2)
D2	=Extract_Amounts(A2,3)
D3	=Extract_Amounts(A3,3)

 

[Kyosti]

So after toiling with the formulas for a few hours, I have discovered that both work well. The cause of the issues were with the verbiage that changed slightly if there was no past due amount input.

Thank you both for the help

 

[jtakw]

If you show a couple of samples where there is no past due amount ( or payment amount, or even amount due ), I can probably tweak my formula to work.

 

[Rick Rothstein]

This is one more approach using UDF (User Defind Function) if interested ...

Function Extract_Amounts(Txt As String, Seq As Integer) As Double
Dim Arr() As Variant, Ref As String, Cnt As Integer, ArCnt As Integer
Cnt = Len(Txt) - Len(Replace(Txt, "$", ""))
ReDim Arr(1 To Cnt)
For x = 1 To Len(Txt)
    If Mid(Txt, x, 1) = "$" Then
        Cnt = x + 1
        Do While Mid(Txt, Cnt, 1) <> " "
            Ref = Ref & Mid(Txt, Cnt, 1)
            Cnt = Cnt + 1
        Loop
            ArCnt = ArCnt + 1
            Arr(ArCnt) = Ref
            Ref = ""
    End If
Next x
Extract_Amounts = Arr(Seq)
End Function

Here is another UDF that could be used. It takes two arguments... the first is the text to be searched and the second is one of these three prhases... "amount due", "past due" or "payment of" (I envision those three phrases to be headers in cells that the UDF formula references).

[table="width: 500"]
[tr]
	[td]Function ExtractAmount(Txt As String, Phrase As String) As Double  ExtractAmount = Val(Split(Split(Txt, Phrase, , vbTextCompare)(1), "$")(1))
End Function[/td]
[/tr]
[/table]

 

[mse330]

That's very clever (and short ) as always Rick ! Just one question though, what does the (1) after the spilt function do ?

Here is another UDF that could be used. It takes two arguments... the first is the text to be searched and the second is one of these three prhases... "amount due", "past due" or "payment of" (I envision those three phrases to be headers in cells that the UDF formula references).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function ExtractAmount(Txt As String, Phrase As String) As Double  ExtractAmount = Val(Split(Split(Txt, Phrase, , vbTextCompare)(1), "$")(1))
End Function[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

That's very clever (and short ) as always Rick ! Just one question though, what does the (1) after the spilt function do ?

Split returns a zero-based array, so the (1) retrieves the second element from that array it creates (which is the text after the text used as the delimiter). You may be thinking that you need to assign the output from the Split function to a String array variable or, alternately, a Variant variable so that you can then retrieve elements from the array that gets assigned to that variable. Of course, that would work, and if you needed to retrieve multiple array elements, it would be the way to code it; however, since we only need to retrieve one element from the array Split returns, you can pull that element out directly from the Split function as the function is returning a real one-dimension, zero-based array.

 

[mse330]

Thanks Rick for the clarification. I just tried (0) instead of (1) & it retuned the first element of the array … That's insightful