Formula please for extract number

[punit83]

Hello Ever1 !

i have tried few formula from forum to extract number from data in cell, it gets attracted but cannot sum as it is extracted as text.
Please any one can share formula in which number can get extract and i can sum them also.

Thanks in advance from newbie

Extract Number & Sum.xlsx
	A	B
1	Date	Result
2	0.07 DR	0.07
3	2.61 DR	2.61
4	20.00 DR	20.00
5	4.08 DR	4.08
6	70.00 DR	70.00
7	0.07 DR	0.07
8	0.07 CR	0.07
9	2.61 CR	2.61
10	20.00 CR	20.00
11	4.08 CR	4.08
12	70.00 CR	70.00
13	0.07 CR	0.07
14	Total	193.66
Sheet1

Cell Formulas
Range	Formula
B14	B14	=SUM(B2:B13)

 

[etaf]

in the extraction formula you use then *1 or +0 to change to a number

i think XL2BB converts to a number, EDIT - yes it does - so you can see the image below - showing the text - left justified and then using *1 to change to a number
any here

Book8
	A	B	C
1	Date	Result
2	0.07 DR	0.07	0.07
3	2.61 DR	2.61	2.61
4	20.00 DR	20.00	20
5	4.08 DR	4.08	4.08
6	70.00 DR	70.00	70
7	0.07 DR	0.07	0.07
8	0.07 CR	0.07	0.07
9	2.61 CR	2.61	2.61
10	20.00 CR	20.00	20
11	4.08 CR	4.08	4.08
12	70.00 CR	70.00	70
13	0.07 CR	0.07	0.07
14	Total	0	193.66
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=LEFT(A2,SEARCH(" ",A2,1))
C2:C13	C2	=LEFT(A2,SEARCH(" ",A2,1))*1
B14:C14	B14	=SUM(B2:B13)

Date	Result
0.07 DR	0.07	0.07​
2.61 DR	2.61	2.61​
20.00 DR	20.00	20​
4.08 DR	4.08	4.08​
70.00 DR	70.00	70​
0.07 DR	0.07	0.07​
0.07 CR	0.07	0.07​
2.61 CR	2.61	2.61​
20.00 CR	20.00	20​
4.08 CR	4.08	4.08​
70.00 CR	70.00	70​
0.07 CR	0.07	0.07​
Total	0​	193.66​

 

[punit83]

in the extraction formula you use then *1 or +0 to change to a number

i think XL2BB converts to a number, EDIT - yes it does - so you can see the image below - showing the text - left justified and then using *1 to change to a number
any here

Book8
	A	B	C
1	Date	Result
2	0.07 DR	0.07	0.07
3	2.61 DR	2.61	2.61
4	20.00 DR	20.00	20
5	4.08 DR	4.08	4.08
6	70.00 DR	70.00	70
7	0.07 DR	0.07	0.07
8	0.07 CR	0.07	0.07
9	2.61 CR	2.61	2.61
10	20.00 CR	20.00	20
11	4.08 CR	4.08	4.08
12	70.00 CR	70.00	70
13	0.07 CR	0.07	0.07
14	Total	0	193.66
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=LEFT(A2,SEARCH(" ",A2,1))
C2:C13	C2	=LEFT(A2,SEARCH(" ",A2,1))*1
B14:C14	B14	=SUM(B2:B13)

Date	Result
0.07 DR	0.07	0.07​
2.61 DR	2.61	2.61​
20.00 DR	20.00	20​
4.08 DR	4.08	4.08​
70.00 DR	70.00	70​
0.07 DR	0.07	0.07​
0.07 CR	0.07	0.07​
2.61 CR	2.61	2.61​
20.00 CR	20.00	20​
4.08 CR	4.08	4.08​
70.00 CR	70.00	70​
0.07 CR	0.07	0.07​
Total	0​	193.66​

Thank you so much and so simple tried so many different and long formula.

 

[etaf]

you are welcome

 

[Tom.Jones]

You can sum directly from column A, if you need:
=SUM(--LEFT(A2:A13,FIND(" ",A2:A13)-1))

 

[punit83]

You can sum directly from column A, if you need:
=SUM(--LEFT(A2:A13,FIND(" ",A2:A13)-1))

Hello Sir,

Thank you for your reply. I tried your formula but not working for me

 

[Tom.Jones]

After you put formula don't press Enter, press CTRL+SHIFT+ENTER

 

[punit83]

After you put formula don't press Enter, press CTRL+SHIFT+ENTER

Yes Sir, Thank you, worked.