Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing a text field to retrieve numeric values
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing a text field to retrieve numeric values
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "u473" wrote in message ... 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing a text field to retrieve numeric values
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 -- Rick (MVP - Excel) "u473" wrote in message ... 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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing a text field to retrieve numeric values
On Sun, 25 Oct 2009 16:52:00 -0700 (PDT), u473 wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing a text field to retrieve numeric values
On Sun, 25 Oct 2009 20:31:35 -0400, "Rick Rothstein"
wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing a text field to retrieve numeric values
Brilliant ! Right on the nose. I was not familiar with that Split
function. Thanks again |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing a text field to retrieve numeric values
On Sun, 25 Oct 2009 22:14:59 -0400, Ron Rosenfeld
wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing a text field to retrieve numeric values
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)) 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 -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keep text out of numeric field | Setting up and Configuration of Excel | |||
Formatting UserForm Text Field as Numeric | Excel Programming | |||
Parsing Last Character in variable length text field | Excel Worksheet Functions | |||
how do i step thru a text field looking for 6 numeric characters | Excel Worksheet Functions | |||
parsing text field based on commas or spaces | Excel Programming |