Sum numbers in the same cell as text

[joeben423]

I download a text file from my bank's website and I want to add all the deposits. The problem is the excel download has the dollar amount and text in the same cell and I cannot add the number because it has text. This is creating an unnecessary step for me because to solve this I would retype the quantity on the B cell, and then I would add it with simple =sum(B1:B8). The problem is I have made mistakes with the retyping of numbers and it's tedious. Please help



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1/2/2018 Depsosit 1000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1/2/2018 Depsosit 1000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1/2/2018 Depsosit 1000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1/2/2018 Depsosit 1000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1/2/2018 Depsosit 1000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]1/2/2018 Depsosit 1000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]1/2/2018 Depsosit 1000[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Formula ?[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

how about either the formulas in colB, or just the formula in C1

Excel 2013/2016
	A	B	C
1	1/2/2018 Depsosit 1000	1000	7000
2	1/2/2018 Depsosit 1000	1000
3	1/2/2018 Depsosit 1000	1000
4	1/2/2018 Depsosit 1000	1000
5	1/2/2018 Depsosit 1000	1000
6	1/2/2018 Depsosit 1000	1000
7	1/2/2018 Depsosit 1000	1000
8		7000
Sheet1

Cell Formulas
Range	Formula
B8	=SUM(B1:B7)
B1	=RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100)*1
C1	{=SUM(RIGHT(SUBSTITUTE(A1:A7," ",REPT(" ",100)),100)*1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Dave Patton]

You could use
- Sumproduct
- formula to extract amount or
- Data Text to Columns

Excel 2010
	A	B	C	D
1	1/2/2018 Deposit 1000	1,000.00
2	1/2/2018 Deposit 1000	1,000.00
3	1/2/2018 Deposit 1000	1,000.00
4	1/2/2018 Deposit 1000	1,000.00
5	1/2/2018 Deposit 1000	1,000.00
6	1/2/2018 Deposit 1000	1,000.00
7	11/2/2018 Deposit 500	500.00
8	6,500.00	6,500.00
9
10	1000
11	1000
12	1000
13	1000
14	1000
15	1000
16	500
17	6,500.00	After using Text to Columns
18
3b

Cell Formulas
Range	Formula
B1	=MID(A1,FIND("Deposit",A1,1)+8,LEN(A1))+0
B8	=SUM(B1:B7)
A8	=SUMPRODUCT(RIGHT(A1:A7,4)+0)
A17	=SUM(A10:A16)

 

[Aladin Akyurek]

A single step solution would be:

=SUMPRODUCT(REPLACE(A1:A7,1,SEARCH("deposit",A1:A7)+6,"")+0)

 

[Rick Rothstein]

Definitely use Aladin's solution if the word deposit is always in front of the trailing number. However, here is another possible single step solution in case the word deposit is not always in front of the trailing number...

=SUMPRODUCT(0+TRIM(RIGHT(SUBSTITUTE(B1:B7," ",REPT(" ",99)),99)))