Split string and copy

[karinos57]

Hi,
Here is what I am trying to do: I am trying to copy a part of a string and copy it into another columns (Package Size and Package Quantity) but there are 3 scenarios in this problem:
1) NEUPOGEN 480M CG/1.6 VL 10X1.6 ML
2) LOVENOX 40 MG PFS 10
3) COMBIVENT 200 ACT INH 14.7 GM
So let me explain here what I want to do here:
Scenario 1) whenever we have ‘X’, everything to the right of ‘X’ is Package Size and everything to the left of ‘X’ is Quantity
Scenario 2) Whenever the last words in a string are just ‘Numbers’ then that number should be the ‘Package Quantity’ and the ‘Package Size’ is ALWAYS ‘1’
Scenario 3) Whenever we have numbers at the end followed by letters like GM or ML at last, then this number should be Package Size and the Package Quantity is ALWAYS 1.

So this is what the example above should look like:
Package Size Package Quantity
Scenario 1) 1.6 10
Scenario 2) 1 10
Scenario 3) 14.7 1

The code below works but would like to make some changes to reflect the example above and the solution could be in any format as long as it works I am fine with it. Pls. see attached sample file. Thanks for your help.

[StephenR]

Cross-posted here

http://www.experts-exchange.com/Prog..._26110356.html

[Leith Ross]

Hello karinos57,

This macro will parse the data and places the results in columns "I" and "J". The offsets are measured from column "B". You can change these to copy the data into the columns you choose. I added a button to run the macro. There are 3 lines highlighted in yellow where the your entries and the parsed data differ.

Please Login or Register  to view this content.

[DonkeyOte]

As outlined this was x-posted but also duped on this forum:

http://www.excelforum.com/excel-work...-and-copy.html

karinos57, if you wish to continue posting on this forum please find the time to read and subsequently adhere to forum rules.

If you continue to run the risk of wasting people's (free) time you will find your posts are soon ignored altogether.