Extract multiple dollar amounts from text in a cell and add together

[TrishaL]

I have cells where there is text and money amounts. I want to extract the money amounts into another cell and if possible, even add the amounts together and give me the total.
Here are couple examples of how it is entered in a cell:
Example 1:
$20,000.00 - Adverse Effect - PAID; $63,000.00 - Tree Loss - PAID; $10,000.00 - Access - PAID; $5,000.00 - Appraisal - PAID; $5,000.00 - Hay Loss - PAID; $5,000.00 - Disturbance - PAID; $5,000.00 - LO Time - PAID; $5,000.00 - Severance - PAID

Example 2:
$12,000.00 - Loss of shelter belt
$7,000.00 - Timber loss
$2,500.00 - Moving equipment
$1,500.00 - Crop loss

Looking for the money to be extracted out so it looks like this: $20,000.00; $63,000.00; $10,000.00; and so on. If possible it would be great to just get the sum of all amounts because my end goal is to pull out the money amount and add together so that I can get a total for all of my rows.

I should point out that not all money is entered the same. There may not be the $ or the .00 or the comma.

If anyone has an idea on if I can do this it would be appreciated. Thank you in advance.

 

[bebo021999]

No, these are vba functions, not worksheet functions.


Probably a good idea to do that, depending on what version(s) you have of course.
Worth updating your profile so others know that information?

excel 2016, done.
With Evaluate(formula), I guess formula must be worksheet function, not VBA.
I will test it with my 2016 version.

 

[TrishaL]

Thanks for updating.


You could test this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down. You could format that column with $/commas/decimal places as desired
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function SumDollars(s As String) As Double
  SumDollars = Evaluate(Replace(Replace(Join(Filter(Split(Replace("$0 " & s, vbLf, " ")), "$"), "+"), "$", ""), ",", ""))
End Function

TrishaL.xlsm
	A	B
1	Data	Sum
2	$20,000.00 - Adverse Effect - PAID; $63,000.00 - Tree Loss - PAID; $10,000.00 - Access - PAID; $5,000.00 - Appraisal - PAID; $5,000.00 - Hay Loss - PAID; $5,000.00 - Disturbance - PAID; $5,000.00 - LO Time - PAID; $5,000.00 - Severance - PAID	118000
3	$12,000.00 - Loss of shelter belt $7,000.00 - Timber loss $2,500.00 - Moving equipment $1,500.00 - Crop loss	23000
4		0
5	No dollars	0
6	$10,000.00 - Tree loss	10000
7	$30,000 - Fencing	30000
8	$1000 - Fencing	1000
9	$3,600.00 - Access Road - PAID; $5,000 - Time/Inconv - PAID; $66,691.20 - Timber - PAID; $5,769.58 - Professional Services (#3) - PAID	81060.78
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=SumDollars(A2)

Thank you very much for this. This worked wonderfully and I was able to get what I needed. This saved me so much time! Again, thank you!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.