Extracting and refreshing data set

[jaredli]

Hello everyone. Can I have vb code for the following:

I have few hundred thousand rows in a column that needs to be separated in two more columns. The column A has information about an order i.e.,

ORDER 00349O1 (PER KG)***[FISHCURRY]***DELIVERED
ORDER 0989378 (PER KG)***[FISHMEAT]***DELIVERED
ORDER 8937494 (PER CTN)***[FISHMEAT]***NOTDELIVERED

The vb code/formula whatever it is should extract the values in parenthesis () in COLUMN B and parenthesis [] in COLUMN C and COLUMN A needs to be refreshed at the same time; should only contain the information without the separated values/texts.

Please help me to sort this out. Thanks everyone and have a nice Easter.

[offthelip]

Hi the formula for column B is:
=MID($A1,1+FIND("(",$A1),FIND(")",$A1)-FIND("(",$A1)-1)

the formula for column c is

=MID($A1,1+FIND("[",$A1),FIND("]",$A1)-FIND("[",$A1)-1)


In a new column instead of column a you can put:
=SUBSTITUTE(SUBSTITUTE(A1,B1,""),C1,"")

which will remove the text found in columns B and C

[jaredli]

Thanks VBAX,

but can it be done without creating a column? I just need a code/formula that creates two columns of separated values being extracted from Column A and at the same time cleans those extracted values from column A. That would be a great help.

[YasserKhalil]

Cross-post at these links

http://chandoo.org/forum/threads/extracting-and-refreshing-data-set.34033/
https://www.excelforum.com/excel-programming-vba-macros/1181697-extracting-and-refreshing-data-set.html

[p45cal]

Cross posted too at: http://www.excelguru.ca/forums/showthread.php?7702-Extracting-and-refreshing-data-set

Select the single column of cells containing your data and run:
Sub blah()
Selection.TextToColumns DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="(", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Selection.TextToColumns DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar:="(", FieldInfo:=Array(Array(1, 9), Array(2, 2)), TrailingMinusNumbers:=True
Selection.Offset(, 1).TextToColumns DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=")", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Selection.Offset(, 2).TextToColumns DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="[", FieldInfo:=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True
Selection.Offset(, 2).TextToColumns DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="]", FieldInfo:=Array(Array(1, 1), Array(2, 9)), TrailingMinusNumbers:=True
End Sub

Don't cross post without telling us. Have a read of http://www.excelguru.ca/content.php?184

[macropod]

Also cross-posted @:
https://www.mrexcel.com/forum/excel-questions/1000810-extracting-refreshing-data-set.html