Ignoring text after number?

afc171

Board Regular
Joined
Jan 14, 2017
Messages
148
Office Version
  1. 2013
Platform
  1. Windows
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
 
Here is my attempt
Book1
AB
123.523.5
24MG4
35 ml5
432.5
Sheet1
Cell Formulas
RangeFormula
B1:B3B1=VALUE(IFERROR(LEFT(A1,MIN(IFERROR(SEARCH(CHAR(SEQUENCE(26,,65,1)),A1),""))-1),A1))
B4B4=SUM(B1:B3)
 
Upvote 0
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.
 
Upvote 0
Maybe:
Book1
AB
15 ml5
223.523.5
34mg4
432.5
Sheet1
Cell Formulas
RangeFormula
B1:B3B1=1/LOOKUP(2,1/--MID(A1,MIN(INDEX(FIND(ROW($1:$10)-1,A1&1/17),,)),ROW(INDIRECT("1:"&LEN(A1)))))
B4B4=SUM(B1:B3)
 
Upvote 0
I am assuming I need an extra column, next to range F8:F17, G8:G17 ?
 
Upvote 0
In 365, try:
Book1
FG
123.532.5
24MG
35 ml
Sheet1
Cell Formulas
RangeFormula
G1G1=SUM(--MAP(F1:F3,LAMBDA(m,TEXTSPLIT(UPPER(m),CHAR(SEQUENCE(26,,65)),,1))))
 
Upvote 0
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.
 
Upvote 0
Book1
FG
85 ml5
923.523.5
104mg4
1132.5
Sheet1
Cell Formulas
RangeFormula
G8:G10G8=1/LOOKUP(2,1/--MID(F8,MIN(INDEX(FIND(ROW($1:$10)-1,F8&1/17),,)),ROW(INDIRECT("1:"&LEN(F8)))))
G11G11=SUM(G8:G10)
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top