Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing a text string in Col. A to extract 2 values and return theirproduct in Col. B
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing a text string in Col. A to extract 2 values and return their product in Col. B
ActiveSheet.Range("B1") = _ Evaluate(Replace(ActiveSheet.Range("A1").Value, "x", "*")) Tim "u473" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing a text string in Col. A to extract 2 values and returntheir product in Col. B
u473 wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing a text string in Col. A to extract 2 values and return their product in Col. B
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 -- Rick (MVP - Excel) "u473" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parsing / seperating text string in excel cell | Excel Discussion (Misc queries) | |||
extract text from string | Excel Worksheet Functions | |||
Extract Values from a String | Excel Programming | |||
Parsing a text string into separate cells | Excel Worksheet Functions | |||
Parsing Data String from text file line | Excel Programming |