Ignoring text after number?

[afc171]

Hi all,
How can I add up totals (range F8:F17) but ignore any text after the number?

23.5

4MG

5 ml

so total would be 32.5 ?

Thank you

 

[Kerryx]

Here is my attempt

Book1
	A	B
1	23.5	23.5
2	4MG	4
3	5 ml	5
4		32.5
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=VALUE(IFERROR(LEFT(A1,MIN(IFERROR(SEARCH(CHAR(SEQUENCE(26,,65,1)),A1),""))-1),A1))
B4	B4	=SUM(B1:B3)

 

[Joe4]

Kerryx,

If they truly are using Excel 2013, as their Profile suggests, the IFERROR and SEQUENCE functions will not be available for them to use, as they came along in later versions of Excel.

 

[Kerryx]

Forgot about that, apologies

 

[mumps]

Maybe:

Book1
	A	B
1	5 ml	5
2	23.5	23.5
3	4mg	4
4		32.5
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=1/LOOKUP(2,1/--MID(A1,MIN(INDEX(FIND(ROW($1:$10)-1,A1&1/17),,)),ROW(INDIRECT("1:"&LEN(A1)))))
B4	B4	=SUM(B1:B3)

 

[afc171]

I am assuming I need an extra column, next to range F8:F17, G8:G17 ?

 

[Cubist]

In 365, try:

Book1
	F	G
1	23.5	32.5
2	4MG
3	5 ml
Sheet1

Cell Formulas
Range	Formula
G1	G1	=SUM(--MAP(F1:F3,LAMBDA(m,TEXTSPLIT(UPPER(m),CHAR(SEQUENCE(26,,65)),,1))))

 

[Joe4]

As mentioned, their Profile says they are using Excel 2013.
If that is accurate, they obviously would not be able to use that formula, as MAP, LAMBDA, and TEXTSPLIT were not around in Excel 2013.

 

[Cubist]

Mixing up @Kerryx with the OP. You can remove my post.

 

[mumps]

Book1
	F	G
8	5 ml	5
9	23.5	23.5
10	4mg	4
11		32.5
Sheet1

Cell Formulas
Range	Formula
G8:G10	G8	=1/LOOKUP(2,1/--MID(F8,MIN(INDEX(FIND(ROW($1:$10)-1,F8&1/17),,)),ROW(INDIRECT("1:"&LEN(F8)))))
G11	G11	=SUM(G8:G10)