• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting and Refreshing Data Set

J

Jared Li

New Member
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.
 
Jared

Firstly, Welcome to the Chandoo.org Forums

Have you tried to use the Data, Text to Columns function.

You may have to do multiple passes to get the right results
otherwise you can use formulas
like:

B2: =MID(A2,7,FIND("(",A2)-8)
C2: =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)
D2: =MID(A2,FIND("[",A2)+1,FIND("]",A2)-FIND("[",A2)-1)
Copy those down
then copy and paste columns B:D as values
Then delete column A
 
Hui said:
Jared

Firstly, Welcome to the Chandoo.org Forums

Have you tried to use the Data, Text to Columns function.

You may have to do multiple passes to get the right results
otherwise you can use formulas
like:

B2: =MID(A2,7,FIND("(",A2)-8)
C2: =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)
D2: =MID(A2,FIND("[",A2)+1,FIND("]",A2)-FIND("[",A2)-1)
Copy those down
then copy and paste columns B:D as values
Then delete column A
Click to expand...

Thank you so much Hui. Can i make this process a bit faster? I would like to run a macro that would extract the values/texts in parentheses to Column B and C and wouldn't show the separated values/texts in Column A. Can it be done?
 
That's my post YasserKhalil. I am looking for an easy solution. that is working fine but since i posted this in the morning today, i've been analyzing the replies i'v got so far. Thanks.
 
Jared Li
Copy Your data to A-column
(from A1 and no empty rows)
and press [Do It]
There were some ... but You'll see what!
 

Attachments

  • JaredLi.xlsb
    41.2 KB · Views: 6
If you had taken a moment to read the forum rules you would have read this piece...

You seem to been posting on a lot of sites, cross-posted here.....
http://www.excelguru.ca/forums/showthread.php?7702-Extracting-and-refreshing-data-set
http://www.excelguru.ca/forums/showthread.php?7702-Extracting-and-refreshing-data-set
https://www.excelforum.com/excel-pr...81697-extracting-and-refreshing-data-set.html
http://www.vbaexpress.com/forum/showthread.php?59189-Extracting-and-refreshing-data-set
http://www.vbaexpress.com/forum/showthread.php?59189-Extracting-and-refreshing-data-set
.
 
I am sorry I wasn't aware of the cross-posting policy being a newbie in forums. I would follow the rules from now onwards. I joined few forums yesterday to have an answer to a problem.

Thanks for correcting me. Peace.
 
I would like to share the best answer i'v got to my question is:

Sub test()
Columns(1).Copy Columns("b:c")
Columns(1).Replace "(*)", "", 2
Columns(1).Replace "[*]", "", 2
Columns(2).Replace "*(", "", 2
Columns(2).Replace ")*", "", 2
Columns(3).Replace "*[", "", 2
Columns(3).Replace "]*", "", 2
End Sub
 
You must log in or register to reply here.
Back
Top