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]

1) could you add more few samples with money amount without $, without ".00"?
2) Any value within text string, all are treated as money amount?
i.e, "Blah blah5000 , $1000, $2,000, $3,000.00 blah 500 blah"
result = $11,500.00?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[TrishaL]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you. I have done this. I am on office 2019 for Windows.

 

[TrishaL]

1) could you add more few samples with money amount without $, without ".00"?
2) Any value within text string, all are treated as money amount?
i.e, "Blah blah5000 , $1000, $2,000, $3,000.00 blah 500 blah"
result = $11,500.00?

Hi
Couple more example types:
Example:
$10,000.00 - Tree loss
Example:
$30,000 - Fencing
Example:
$1000 - Fencing
Example
$3,600.00 - Access Road - PAID; $5,000 - Time/Inconv - PAID; $66,691.20 - Timber - PAID; $5,769.58 - Professional Services (#3) - PAID

I reviewed further and it looks like all money does have the $. Looks like just the comma and .00 are the issue.

Yes, if there is a way to extract as you say above to get the total that would be awesome. Then I can do this in just one step.

Thanks,

 

[Peter_SSs]

I have done this. I am on office 2019 for Windows.

Thanks for updating.

I reviewed further and it looks like all money does have the $. Looks like just the comma and .00 are the issue.

Yes, if there is a way to extract as you say above to get the total that would be awesome. T

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)

 

[bebo021999]

Or, alternative for excel older than version 2019:

Option Explicit
Function summary(ByVal cell As Range)
Dim s, i&, k&, num, val, comb, sum As Double
s = Split(cell.Value, "$")
For i = 1 To UBound(s)
    k = 0: val = 0: comb = ""
    num = Replace(s(i), ",", "")
    Do While IsNumeric(val) Or val Like "."
        k = k + 1
        val = Mid(num, k, 1)
        comb = comb & val
    Loop
    sum = sum + CDbl(comb)
Next
summary = sum
End Function

Book1.xlsb
	A	B
1	$25,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
2	$10,000.00 - Tree loss
3	$30,000 - Fencing
4	No dollar
5	$3,600.00 - Access Road - PAID; $5,000 - Time/Inconv - PAID; $66,691.20 - Timber - PAID; $5,769.58 - Professional Services (#3) - PAID
6
7
8	123,000.00	=summary(A1)
9	10,000.00	=summary(A2)
10	30,000.00	=summary(A3)
11	-	=summary(A4)
12	81,060.78	=summary(A5)
Sheet4

Cell Formulas
Range	Formula
A8:A12	A8	=summary(A1)

 

[Peter_SSs]

Or, alternative for excel older than version 2019:

Does that imply that some part of my suggestion is not compatible with older versions?

For a looping version I would consider

Function DollarSum(s As String) As Double
  Dim itm As Variant
 
  For Each itm In Split(Replace(s, ",", ""), "$")
    DollarSum = DollarSum + Val(itm)
  Next itm
End Function

 

[bebo021999]

Does that imply that some part of my suggestion is not compatible with older versions?

Not test, but Joint, Filter is for Ex365 (or maybe 2019?) ?

 

[Peter_SSs]

Joint, Filter is for Ex365 (or maybe 2019?) ?

No, these are vba functions, not worksheet functions.

Not test,

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?