• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

The equation for finding the product of two numbers by a cell with text

Hany ali

Active Member
hello my Master, I want Yoy Help to Get The Result for this Operation
I want The Result to Be Start from B2 For SIZE in A2 ....as you see when the cell Including Text , I get error in my Result
Please find the necessary equation for this case, and I thank you very much
 

Attachments

  • 1.png
    1.png
    11.5 KB · Views: 15
  • Carpets Size.xlsm
    16.4 KB · Views: 10
Please try

Code:
Function Evals(t As String) As Double
Dim c As String, i As Long
For i = 1 To Len(t)
    If Asc(Mid(t, i, 1)) < 58 And Asc(Mid(t, i, 1)) > 41 Then c = c & Mid(t, i, 1)
Next
Evals = Evaluate(c)
End Function

B2 =Evals(A2)
 
Thanks alot @Excel Wizard
You can use elso this Code
Code:
Option Explicit
Sub test()
    Dim a As Variant
    Dim m As Object
    Dim r, i
    r = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(\d.*\d)"
        For i = 2 To r
            Set m = .Execute(Cells(i, 1))
            a = Split(m(0), "*")
            Cells(i, 1).Offset(, 1) = a(0) * a(1)
        Next
    End With
End Sub
 
Excel Wizard
herofox

Thank you very much to all of you, all are excellent solutions. The least thing is said about you in this case that you are creative..and it is really what is required of work.
 
It is possible to do the same thing with spreadsheet formulas. It is at its simplest in Excel 365
Code:
Let 'size' refer to:
= LET(
  n, LEN(@string),
  k, SEQUENCE(1,n),
  chrs, MID(@string, k, 1),
  CONCAT( IF( CODE(chrs)<64, chrs, "") ) )

and then 'result'
= EVALUATE(size)
 
You could use defined names to hold the formulas instead of the local names in LET. I have still used SEQUENCE to generate the indices for MID.
71759
 
sorry,but this way Not work with me ,because i have Error in this Define
chrs=MID(@string,SEQUENCE(1,LEN(string)),1)
 
Non VBA formula
B2 for MS365
=PRODUCT(-MID(SUBSTITUTE(CONCAT(REPT(MID(A5,SEQUENCE(LEN(A5)),1),CODE(MID(A5,SEQUENCE(LEN(A5)),1))<58)),"*",REPT(" ",20)),{1,21},20))

C2 for Excel 2010
=PRODUCT(-MID(A5,AGGREGATE(15,6,ROW(A$1:A$30)/((MID(A5,ROW($A$1:$A$30),1)=".")+ISNUMBER(-MID(A5,ROW($A$1:$A$30),1)))/(MID("@"&A5,ROW($A$1:$A$30),1)<>".")/ISERR(-MID("@"&A5,ROW($A$1:$A$30),1)),{1,2}),MOD(AGGREGATE(15,6,ROW($A$1:$A$30)*100+(1/(1/FREQUENCY(IF((MID(A5,ROW($A$1:$A$30),1)=".")+ISNUMBER(-MID(A5,ROW($A$1:$A$30),1)),ROW($A$1:$A$30)),IF((MID(A5,ROW($A$1:$A$30),1)<>".")*ISERR(-MID(A5,ROW($A$1:$A$30),1)),ROW($A$1:$A$30))))),{1,2}),100)))
Ctrl+Shift+Enter
 

Attachments

  • Multiply text.xlsx
    10.7 KB · Views: 7
What version of Excel are you using? I develop for Excel 365; then it is a question of how far to regress the solution to produce something that works in older versions. I have replaced the formula 'chrs' that returns an array of individual characters by
= MID(string, ROW( INDIRECT("1:"&LEN(string)) ), 1 )
which, I think, should work with any Excel version back to Office 2003 (fingers crossed).
 

Attachments

  • Carpets Size.xlsm
    16.9 KB · Views: 4
Thank you very much, Professor, for all these attempts, but as you know, this method did not work for me because it already includes equations from Excel 365.
And that as you can see in this picture
 

Attachments

  • Untitled.png
    Untitled.png
    79.1 KB · Views: 5
Sorry, you are right, I had removed the new dynamic array formulas from Excel 365, but forgot that CONCAT was also a new function (it is in Excel 2019 but missed the cut on Excel 2016). As I am taken back in time, the solutions get increasingly displeasing aesthetically, so now is time to bail out; I would use one of the VBA solutions.
 
Back
Top