Extract dollar amount from text in range, then total those amounts on summary

[DDRA Steampunk]

Hi End users will be just pasting the monthly report CSV into the helper template. I'm working with another company's data so I'm forbidden from just fixing the horrible format where the dollar amounts are in a sentence like "$37.84 sent to your bank account". The formula to get each numeric from the text was easy, but I have no idea how to then get it to Sum without using some kind of helper column. I believe there is a VBA function to just run the entire range through the formula and aggregate the results. I could be wrong though! Would also accept a formula based solution.

Number of pages is fixed to 15: Index, Terminology, Summary, 12 x Month page
Columns of Data are fixed to A through H: Date, Type, Info, Currency (could be USD or CAD), Amount, Fees & Taxes, Net Balance
Rows are expandable, clients may have anything between 0 and theoretically infinite number of transactions

Formula currently used to extract the Numeric Value: =(TRIM(MID(C3,FIND("$",C3)+1,FIND(" ",C3)-FIND("$",C3)-1)))

Current Sum example: =SUMIF(January!$B:$B,"deposit",January!$F:$F)
Side note, I think the reason I can't pop the month name in by Indirect is because it's full column as Range? I've only used indirect looking for a particular cell so limited experience.

 

[Micron]

In vba, this is how I might get the dollar amount: Val(mid(yourRangeHere,2)). In immediate window using my selected range:

?val(mid(sheets("sheet6").range("d5"),2))
returns 37.84
To sum them you'd have to loop over the range(s), but what good would it do to add $10 US to $10 CDN? If you need separate sums based on currencies it will be more complicated.

 

[Fluff]

How about using

=TRIM(MID(C3,FIND("$",C3)+1,FIND(" ",C3)-FIND("$",C3)-1))+0

 

[DDRA Steampunk]

In vba, this is how I might get the dollar amount: Val(mid(yourRangeHere,2)). In immediate window using my selected range:

?val(mid(sheets("sheet6").range("d5"),2))
returns 37.84
To sum them you'd have to loop over the range(s), but what good would it do to add $10 US to $10 CDN? If you need separate sums based on currencies it will be more complicated.

Ah, I wasn't clear there. Each user has 1 currency but different users have different currencies from each other. I wasn't sure if there would be any reason that might matter. Thanks!

 

[DDRA Steampunk]

What do I put in the VBA to return the total into the specific cell on the summary page? I'm sorry I'm being dumb about this! Lately it's like nothing makes sense to me.

 

[Micron]

I guess the way you reference the target cell depends on where the code is located. How about

Sheets("SummaryPageName").Range("CellAddressGoesHere") = val(mid(sheets("sheet6").range("d5"),2))

I imagine there's tons of posted code examples on how to loop over a range using vba if that's where you're headed.