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.

 

[Kyosti]

please be aware that the dollar values may vary.

 

[Kyosti]

I am giving this a bump as I really do need assistance!! Please help!

 

[AhoyNC]

There is probably a better way, but this seems to work. I changed your dollar amounts to check that it was picking up the correct data.

Excel Workbook
	A	B	C	D
4		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.25 that is due before 8/14 to avoid cancellation of the policy. To prevent a loss of coverage, F made a payment of $20.25 with (CPay). The reference number for the payment made is (reference).
5
6			Amount Due	10
7			Past Due	10.25
8			Payment	20.25
Sheet

 

[Kyosti]

LIFE SAVER!!!!! Thanks so much!! It does work perfectly

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[Kyosti]

I may have spoken too soon. When I tried applying the formula to another text, the columns did not pull the data as expected. The text is the same the only difference is the dollar amounts.

 

[AhoyNC]

How about posting a copy of the text that is causing the error.

 

[Kyosti]

So this is one of the other text strings. The error appears on the third value where a payment is being made, which of course is the most important one. The text only changes on customer information and payment amount so I don't know why it works on the first example, but not the second!!! Please help!!

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

 

[jtakw]

Hi,

Try this:

Book1
	B	C	D	E	F
4	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
5	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
Sheet167

Cell Formulas
Range	Formula
D4	=LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE($B4,"$",REPT(" ",LEN($B4)),COLUMNS($D4:D4)),LEN($B4),LEN($B4)))," ",REPT(" ",15)),15)+0

D4 formula copied down and across to F5.

EDIT: If you want the results Vertically placed ( In Same Column ) rather than Horizontally as in my sample, my formula needs to be modified.

Let me know.