Splitting cells with 2 delimiters "; and |", can it be done?

[gadgetic]

Hi

This is my first post. I hope that it makes sense. I export a sales csv from my website which contains all the address information for the various couriers that I use. Recently orders overseas have increased and I am forever filling in CN22 forms, so I decided to also export out item information. I figured to keep things simple, I would keep one row per one order, but getting multiple item data onto the same row meant I had to use some new delimiters.

All the address information is "comma" separated but now I have introduced a new column called "Item Information" which has 3 bits of data

- Item Name
- Item Price
- Currency

They are separated by ";"

If there is more than 1 item then I use "|" to seperate.

This way all the Item Information is inside one cell and on the same row as the address.

Here is an example:

Thin Strap Plain Camisole;10.5094;USD|Thin Strap Plain Camisole;10.4183;USD|Thin Strap Plain Camisole;10.6157;USD|

Now what I need to be able to do is split the above data into separate cells in a meaningful way, so that I can list the information in a table on a CN22, where I can total the prices at the bottom.

Can anybody help? Preferably I would like some vba macro code, so that this task can be automated.

Many Thanks

Gurpreet

 

[AlphaFrog]

[COLOR=darkblue]Sub[/COLOR] Some[COLOR=darkblue]Sub[/COLOR]()
    Range("Z2", Range("Z" & Rows.Count).End(xlUp)).TextToColumns _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, _
        Comma:=False, Space:=False, Other:=True, OtherChar:="|"
[COLOR=darkblue]End[/COLOR] Sub

 

[gadgetic]

Thank you guys! Rick your code works very fast!

 

[Mike LH]

Hi,

Of course you're correct, I should have remembered that. Thanks.