Parsing a text string in Col. A to extract 2 values and return theirproduct in Col. B

[u473]

I need to process to parse a text string in column A to extract 2
values and return their product in colunmn B
A B
120x150 18000

It gets worse when sometimes in the same Col. A I get 2 sets of 2
values like in the following case
40x40x120x120 16000

First I have to test whether I have to deal with the first or second
case
but then I get lost in handling the Instr and InstrRev functions to
extract each set of values
Help appreciated

 

[Tim Williams]

ActiveSheet.Range("B1") = _
Evaluate(Replace(ActiveSheet.Range("A1").Value, "x", "*"))

Tim

 

[smartin]

I need to process to parse a text string in column A to extract 2
values and return their product in colunmn B
A B
120x150 18000

It gets worse when sometimes in the same Col. A I get 2 sets of 2
values like in the following case
40x40x120x120 16000

First I have to test whether I have to deal with the first or second
case
but then I get lost in handling the Instr and InstrRev functions to
extract each set of values
Help appreciated

Try this out:

Sub mult()
Dim c As Range
For Each c In Application.Intersect(Range("A:A"), _
ActiveSheet.UsedRange)
c.Offset(0, 1) = Application.Evaluate(Replace(c.Text, "x", "*"))
Next
End Sub

 

[Rick Rothstein]

It is really hard to give you an answer because your 2nd example makes no
sense. Your first example seems to suggest multiplication, but no two or
more values in your second example can be multiplied to give 16000. If, on
the other hand, you meant 1600, then I would guess you want to multiply the
first two numbers no matter how many other values came after them. If that
is the case, then try this macro...

Sub MultiplyFirstTwoValues()
Dim X As Long, LastRow As Long, Numbers As Variant
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
For X = 1 To LastRow
Numbers = Split(Cells(X, "A").Value, "x", , vbTextCompare)
Cells(X, "B").Value = Numbers(0) * Numbers(1)
Next
End Sub