Extracting numbers from a text and multiplying them numbers eachother

cadandcode

Board Regular
Joined
Jan 21, 2023
Messages
125
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
Hello, I checked most of the topics but couldnt find the exact formula.

AB
14 M 10 AD40
220 M 7 AD140

I would like to get B2 results just like that from A1 and A2 columns. Extract 4 and 10 from A1 and multiply them eachother in B1 (B1 is just an example) like I tried to describe above.
I found a vba code to extract 4 and 10 from that text cell into B1 like "410" but I couldnt find a way to multiply them as "=4*10" in B1 instead of putting them together like "410".
 
As I said, you are probably better off putting the formula into B1 to B4 & then add up the results.
 
Upvote 0

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
You could try
Excel Formula:
=LET(a,FILTERXML("<k><m>"&SUBSTITUTE(TEXTJOIN(" ",,A2:A10)," ","</m><m>")&"</m></k>","//m[.=number()]"),r,ROWS(a)/2,SUMPRODUCT(INDEX(a,SEQUENCE(r,,,2)),INDEX(a,SEQUENCE(r,,2,2))))
but this will only work in 2021, not 2019
 
Upvote 0
Another way

EXCEL
MN
244 M 10 AD40
2520 M 7 AD140
Sheet2
Cell Formulas
RangeFormula
N24:N25N24=PRODUCT(IFERROR(TEXTSPLIT(M24," ")+0,1))
 
Upvote 0
You could try
Excel Formula:
=LET(a,FILTERXML("<k><m>"&SUBSTITUTE(TEXTJOIN(" ",,A2:A10)," ","</m><m>")&"</m></k>","//m[.=number()]"),r,ROWS(a)/2,SUMPRODUCT(INDEX(a,SEQUENCE(r,,,2)),INDEX(a,SEQUENCE(r,,2,2))))
but this will only work in 2021, not 2019
Does 365 contain all of the coding/formula content such as you shared above?
 
Upvote 0
Given that post 6 indicates all values have the same structure (post 4 worked with semicolon) I think these may also be options.
Not sure if the B2 formula would work as-is in 2021 or might need Ctrl+Shift+Enter, otherwise I think C2 should work in both your versions (once you swap commas for semi-colons)

23 01 22.xlsm
ABC
1
24 M 10 AD206206
320 M 7 AD
4
513 M 2 AD
6
cadandcode
Cell Formulas
RangeFormula
B2B2=LET(x,SUBSTITUTE(SUBSTITUTE(0&A2:A100," AD",""),"M",REPT(" ",20)),SUM(LEFT(x,20)*RIGHT(x,20)))
C2C2=SUMPRODUCT(LEFT(SUBSTITUTE(SUBSTITUTE(0&A2:A100," AD",""),"M",REPT(" ",20)),20)*RIGHT(SUBSTITUTE(SUBSTITUTE(0&A2:A100," AD",""),"M",REPT(" ",20)),20))
 
Upvote 0
Given that post 6 indicates all values have the same structure (post 4 worked with semicolon) I think these may also be options.
Not sure if the B2 formula would work as-is in 2021 or might need Ctrl+Shift+Enter, otherwise I think C2 should work in both your versions (once you swap commas for semi-colons)

23 01 22.xlsm
ABC
1
24 M 10 AD206206
320 M 7 AD
4
513 M 2 AD
6
cadandcode
Cell Formulas
RangeFormula
B2B2=LET(x,SUBSTITUTE(SUBSTITUTE(0&A2:A100," AD",""),"M",REPT(" ",20)),SUM(LEFT(x,20)*RIGHT(x,20)))
C2C2=SUMPRODUCT(LEFT(SUBSTITUTE(SUBSTITUTE(0&A2:A100," AD",""),"M",REPT(" ",20)),20)*RIGHT(SUBSTITUTE(SUBSTITUTE(0&A2:A100," AD",""),"M",REPT(" ",20)),20))
My main language is Turkish, so C3 (the formula in B2 cell didnt work due version differecen, you are right) cell gave me #AD? (#NAME? in English) error. I changed excel's language to English and solved problem but I cant keep using Excel in English. Have to change it to Turkish back. So how to solve that #NAME? (#AD?) error in Turkish with that original formula you've given in C3 cell?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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