Extract only currency value from a string

[78459430]

$10,000 received on 10/10/2023
$500 received on 10/10/2023	Total received on 10/10/2023 is $10,500
$1,500 received on 11/10/2023
$1,000 received on 11/10/2023	Total received on 11/10/2023 is $2,500
$50 received on 12/10/2024
$150 received on 12/10/2024	Total received on 12/10/2024 is $200
$500 received on 15/10/2024
$200 received on 15/10/2024	Total received on 15/10/2024 is $700

Hi,
I would need the formulas on column B to read the exact date and SUM only the currency value ($xxxxxxx) shown on column A.
Please help

Thank you!

 

[Peter_SSs]

Would something like this be any use?

25 03 26.xlsm
	A	B	C
1	Data	Date	Total Received
2	$10,000 received on 10/10/2023	10/10/2023	$10,500
3	$500 received on 10/10/2023	11/10/2023	$2,500
4	$1,500 received on 11/10/2023	12/10/2024	$200
5	$1,000 received on 11/10/2023	15/10/2024	$700
6	$50 received on 12/10/2024
7	$150 received on 12/10/2024
8	$500 received on 15/10/2024
9	$200 received on 15/10/2024
Sum by date

Cell Formulas
Range	Formula
B2:C5	B2	=LET(d,UNIQUE(TEXTAFTER(A2:A9," ",-1)),HSTACK(--d,BYROW(d,LAMBDA(r,SUM(--FILTER(TEXTBEFORE(A2:A9," "),TEXTAFTER(A2:A9," ",-1)=r))))))
Dynamic array formulas.

 

[saboh12617]

Hello,

With a regex :

=VALUE(REGEXEXTRACT(A1, "\$\s*([0-9,.]+)", 2))

Nota: value will only work if your number system is the same as the one of the "text" ie. thousand separator = "," and decimal separator =".". Otherwise you first need to swap them to yours (in Europe space and comma respectively).

 

[78459430]

Hello,

With a regex :

=VALUE(REGEXEXTRACT(A1, "\$\s*([0-9,.]+)", 2))

Nota: value will only work if your number system is the same as the one of the "text" ie. thousand separator = "," and decimal separator =".". Otherwise you first need to swap them to yours (in Europe space and comma respectively).

Thank you! Perhaps I missed some info... I would need the cell B2 to read all cells on Column 1 and get the result based on the exact date (the exact day is something that I can write in the formula).

 

[saboh12617]

Hi sorry I did misread your question.
I see that Peter_SS provided the answer tho. As long as there are no space between the "$" and the number his proposition is optimal.

 

[Dave Patton]

T202503a.xlsm
	I	J
1	Date	Total Received
2	10-Oct-23	$10,500
3	11-Oct-23	$2,500
4	12-Oct-24	$200
5	15-Oct-24	$700
6	Total	$13,900
7
8
7b

Cell Formulas
Range	Formula
I2:J6	I2	=GROUPBY(--TEXTAFTER(A2:A9," ",-1),--TEXTBEFORE(A2:A9," "),SUM)
Dynamic array formulas.

 

[Peter_SSs]

Thank you! Perhaps I missed some info... I would need the cell B2 to read all cells on Column 1 and get the result based on the exact date (the exact day is something that I can write in the formula).

No comment on the post 2 suggestion?
Perhaps it is this you want instead?

25 03 26.xlsm
	A	B
1	Data	Results
2	$10,000 received on 10/10/2023
3	$500 received on 10/10/2023	Total received on 10/10/2023 is $10,500
4	$1,500 received on 11/10/2023
5	$1,000 received on 11/10/2023	Total received on 11/10/2023 is $2,500
6	$50 received on 12/10/2024
7	$150 received on 12/10/2024	Total received on 12/10/2024 is $200
8	$500 received on 15/10/2024
9	$200 received on 15/10/2024	Total received on 15/10/2024 is $700
Sum by date (2)

Cell Formulas
Range	Formula
B2:B9	B2	=LET(a,TEXTBEFORE(A$2:A2," "),d,TEXTAFTER(A$2:A2," ",-1),v,TAKE(d,-1),IF(v=TEXTAFTER(" "&A3," ",-1),"","Total received on "&v&" is "&TEXT(SUM(--FILTER(a,d=v)),"$#,##0")))

 

[78459430]

No comment on the post 2 suggestion?
Perhaps it is this you want instead?

This is almost what I need.
I would just need the formula on cell B-9 to read all column A (cells A2-A9) and get the SUM of the "currency values" based on the date that I will type in the formula.
Like this: "formula, please, get the sum of the dollars received on 10/10/2023" and the formula will say "Total received on 10/10/2023 is $10,500"

 

[Peter_SSs]

based on the date that I will type in the formula.

I would recommend against typing the date in the formula & suggest putting it in cell then using the formula to reference that cell as shown in B2:C3 below.
If you really must type it in the formula I have given an example in cell C5.

If this is still not what you want please post new sample data with the exact result(s) that you want (preferably with XL2BB ) and explain again in relation to that specific sample data.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

25 03 26.xlsm
	A	B	C
1	Data	Date	Result
2	$10,000 received on 10/10/2023	11/10/2023	Total received on 11/10/2023 is $2,500
3	$500 received on 10/10/2023	5/10/2023	Total received on 5/10/2023 is $0
4	$1,500 received on 11/10/2023
5	$1,000 received on 11/10/2023		Total received on 10/10/2023 is $10,500
6	$50 received on 12/10/2024
7	$150 received on 12/10/2024
8	$500 received on 15/10/2024
9	$200 received on 15/10/2024
Sum by date (3)

Cell Formulas
Range	Formula
C2:C3	C2	=LET(d,TEXT(B2,"d/mm/yyyy"),"Total received on "&d&" is "&TEXT(SUM(--FILTER(TEXTBEFORE(A$2:A$9," "),TEXTAFTER(A$2:A$9," ",-1)=d,0)),"$#,##0"))
C5	C5	=LET(d,"10/10/2023","Total received on "&d&" is "&TEXT(SUM(--FILTER(TEXTBEFORE(A$2:A$9," "),TEXTAFTER(A$2:A$9," ",-1)=d,0)),"$#,##0"))

 

[HighAndWilder]

This is almost what I need.
I would just need the formula on cell B-9 to read all column A (cells A2-A9) and get the SUM of the "currency values" based on the date that I will type in the formula.
Like this: "formula, please, get the sum of the dollars received on 10/10/2023" and the formula will say "Total received on 10/10/2023 is $10,500"

I question putting the date in a formula but here you go:

=LET(d,DATEVALUE("10/10/2023"),r,A2:A9,a,GROUPBY(--TEXTAFTER(r," ",-1),--TEXTBEFORE(r," "),SUM,0,0),"Totally received on "&TEXT(CHOOSECOLS(FILTER(a,INDEX(a,,1)=d),1),"DD/MM/YYYY")&" is $"&CHOOSECOLS(FILTER(a,INDEX(a,,1)=d),2))