extract and sum data values beginning with a certain character

[kbastiaans]

Hi there!

I have been racking my brain on how to do this - and I am not sure if it is even possible... here is what I am trying to do: I need to extract all monetary values from the sample text string below that start with the $ character and then sum them together. The character string can have multiple entries all divided by a semicolon.

example data in cell A1:

Jones Inc. - $41,000 - february; Abc co - $5,609 - January; Jen's Place - $12,432 - march; ...


what I want to get for the final output is a cell that sums all of the numeric values (e.g. 41000+5609+12432+...)

Can this even be done??

 

[SpillerBD]

The data should be parsed into a proper data-set first.
You have record delimiters ";" and field delimiters "-"
Once parsed, you will have a regular data set in an Excel "frame-of-mind"

Does this data start in a Text file?
What version of Excel are you using?

 

[MickG]

Try this:-
Your data in "A1"

[COLOR="Navy"]Sub[/COLOR] MG09Mar30
[COLOR="Navy"]Dim[/COLOR] sp [COLOR="Navy"]As[/COLOR] Variant, S [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Tot [COLOR="Navy"]As[/COLOR] Double, Num [COLOR="Navy"]As[/COLOR] Double
sp = Split([a1], "-")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] S [COLOR="Navy"]In[/COLOR] sp
     [COLOR="Navy"]If[/COLOR] IsNumeric(Mid(Trim(S), 2, Len(Trim(S)) - 1)) [COLOR="Navy"]Then[/COLOR]
            Num = Num + Mid(Trim(S), 2, Len(Trim(S)) - 1)
     [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] S
MsgBox Num
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can use...

Function SumDollars(S As String) As Double
  Dim V As Variant
  For Each V In Split(S, "$")
    SumDollars = SumDollars + Val(Replace(V, ",", ""))
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SumDollars just like it was a built-in Excel function. For example,

=SumDollars(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[kbastiaans]

That worked perfectly! Thanks so much for your help - I never would have figured that out in a million years!