Parsing a text field to retrieve numeric values

[u473]

I have been given a table in wich each cell of column A contains the
following
text format for 4 numeric values.
Value1 x Value2 x Value3 x Value4
like 30x140x50x120

How do I retrieve each of the 4 numeric values,
and populate column B as (Value1 * Value2 ) + (Value3 * Value4)

The second "x" multiplier is misleading and could have been left out
but this is how the table was improperly populated and I have to cope
with it.

Help sincerely appreciated,

 

[Bernard Liengme]

You could do some fancy stuff with MID but I expect the pattern of digits is
not always 2,3,2,3 so you would need to use FIND within MID. A lot of
trouble!

Just select the column of text and use Date | Text to Columns specifying
Delimited by x
Now you have the real numbers and can whatever math you like
best wishes

 

[Rick Rothstein]

Do you still have the mix of 2 and 4 number text as you posted in your
previous thread? I'll assume so. Here is the code I posted in your original
thread, modified to handle the 4 number situation as you have now
described...

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)
If UBound(Numbers) = 1 Then
Cells(X, "B").Value = Numbers(0) * Numbers(1)
Else
Cells(X, "B").Value = (Numbers(0) * Numbers(1)) + _
(Numbers(2) * Numbers(3))
End If
Next
End Sub

 

[Ron Rosenfeld]

I have been given a table in wich each cell of column A contains the
following
text format for 4 numeric values.
Value1 x Value2 x Value3 x Value4
like 30x140x50x120

How do I retrieve each of the 4 numeric values,
and populate column B as (Value1 * Value2 ) + (Value3 * Value4)

The second "x" multiplier is misleading and could have been left out
but this is how the table was improperly populated and I have to cope
with it.

Help sincerely appreciated,

If your format is exactly how you show it, then:

=LEFT(A1,FIND("x",A1)-1)*TRIM(MID(SUBSTITUTE(
A1,"x",REPT(" ",99)),99,99))+TRIM(MID(SUBSTITUTE(
A1,"x",REPT(" ",99)),198,99))*TRIM(RIGHT(
SUBSTITUTE(A1,"x",REPT(" ",99)),99))

should work.

Another approach would be:

=LEFT(A1,FIND("x",A1)-1)*MID(A1,FIND("x",A1)+1,
FIND(CHAR(1),SUBSTITUTE(A1,"x",CHAR(1),2))-
FIND("x",A1)-1)+MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1,"x",CHAR(1),2))+1,FIND(CHAR(1),SUBSTITUTE(
A1,"x",CHAR(1),3))-FIND(CHAR(1),SUBSTITUTE(
A1,"x",CHAR(1),2))-1)*TRIM(RIGHT(SUBSTITUTE(A1,"x",REPT(" ",99)),99))

or, you could use a User Defined Function (UDF).

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=Proc(A1)

in some cell.

====================================
Function Proc(s As String) As Double
Proc = Evaluate(WorksheetFunction.Substitute _
(Replace(s, "x", "*"), "*", "+", 2))
End Function
--ron

 

[Ron Rosenfeld]

Do you still have the mix of 2 and 4 number text as you posted in your
previous thread? I'll assume so. Here is the code I posted in your original
thread, modified to handle the 4 number situation as you have now
described...

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)
If UBound(Numbers) = 1 Then
Cells(X, "B").Value = Numbers(0) * Numbers(1)
Else
Cells(X, "B").Value = (Numbers(0) * Numbers(1)) + _
(Numbers(2) * Numbers(3))
End If
Next
End Sub

I know you're fond of one-liners:

Evaluate(WorksheetFunction.Substitute(Replace(s, "x", "*"), "*", "+", 2))

--ron

 

[u473]

Brilliant ! Right on the nose. I was not familiar with that Split
function.
Thanks again

 

[Ron Rosenfeld]

I know you're fond of one-liners:

Evaluate(WorksheetFunction.Substitute(Replace(s, "x", "*"), "*", "+", 2))

Hmmm, forgot the rest of the code, although the above line has the essence:

===============
Function Proc(s As String) As Double
Proc = Evaluate(WorksheetFunction.Substitute _
(Replace(s, "x", "*"), "*", "+", 2))
End Function
=====================
--ron

 

[Rick Rothstein]

Do you still have the mix of 2 and 4 number text as you posted in your

I know you're fond of one-liners:

Evaluate(WorksheetFunction.Substitute(Replace(s, "x", "*"), "*", "+", 2))

To Ron: Yes, I do... and that one looks good to me!

To u473: To implement this in a macro (as opposed to a UDF which Ron gave
you directly in his response to you), it would be done this way...

Sub MultiplyAddCellValues()
Dim X As Long
For X = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(X, "B").Value = Evaluate(WorksheetFunction.Substitute(Replace( _
Cells(X, "A").Value, "x", "*"), "*", "+", 2))
Next
End Sub