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]

What column is the two-delimiter data in?
Is there any filled columns to the right of this data column?

 

[gadgetic]

The data is in column Z, and all columns to the right are empty.

 

[Mike LH]

Hi,

You can have a macro if you want one but this is simple enough with a formula. With the first string in Z2, put this in AA2 and drag right


=TRIM(MID(SUBSTITUTE(";" & SUBSTITUTE($Z2,"|",";")&REPT(" ",6),";",REPT(" ",255)),COLUMN(A1)*255,255))

 

[oldbrewer]

try searching for | say it is at character 27 left(26) is first item mid(27,250) is the rest - now search for | in the rest and repeat, and repeat

all can be done in helper cells out of sight

 

[Rick Rothstein]

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.

You have to remember that you are addressing a world-wide audience. I, for one, have no idea what a CN22 form is. It would be helpful if you told us what the meaningful separation of the above example text line would look like, then we can provide code to do that (we do not really need to know how you plan to use it afterward, we just need to know how you want the parsed text to look before you do whatever comes next).

 

[gadgetic]

Hi Mike

That is awesome! Works like a dream, however the reason why I want it in a macro, is because I run a lot of courier rules logic through some vba code, so a small snippet to accomplish this task could just be added to that code. I will try and convert your furmula into some vba code, but if you have time I would really appreciate it!

Many Thanks

Gurpreet

 

[gadgetic]

Hi Rick

I am sorry about that. Yes, a CN22 form is a necessary customs declaration when anything is sent outside of Europe from the UK. So on it, I need to state what goods are in the package, the cost of each and the total cost in the correct method of payment currency.

Once I can split the data from one cell, I intend to then feed it into some thermal label software that can pick up the individual fields as well as the address fields, and create my CN22 table on the same stcker as the address. This means, I dont need to hand fill in a separate CN22 sticker for each package which leaves Europe.

I hope that adds some needed purpose for my task.

Many Thanks

Gurpreet

 

[Mike LH]

Hi Mike

That is awesome! Works like a dream, however the reason why I want it in a macro, is because I run a lot of courier rules logic through some vba code, so a small snippet to accomplish this task could just be added to that code. I will try and convert your furmula into some vba code, but if you have time I would really appreciate it!

Many Thanks

Gurpreet

No problem , Here it is as a macro

Sub SomeSub()
Dim Lastrow As Long, MyRange As Range
Dim C As Range
Lastrow = Cells(Rows.Count, "Z").End(xlUp).Row
Set MyRange = Range("Z2:Z" & Lastrow)
For Each C In MyRange
C.TextToColumns Destination:=C.Offset(, 1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=True, OtherChar:= _
        "|"
Next
End Sub

 

[Rick Rothstein]

No problem , Here it is as a macro

Sub SomeSub()
Dim Lastrow As Long, MyRange As Range
Dim C As Range
Lastrow = Cells(Rows.Count, "Z").End(xlUp).Row
Set MyRange = Range("Z2:Z" & Lastrow)
For Each C In MyRange
C.TextToColumns Destination:=C.Offset(, 1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=True, OtherChar:= _
        "|"
Next
End Sub

TextToColumns does not require a loop... it can work on the entire column all at once...

Sub SomeSub()
  With Range("Z2", Cells(Rows.Count, "Z").End(xlUp))
    .TextToColumns .Offset(, 1), xlDelimited, xlDoubleQuote, _
                   False, False, True, False, False, True, "|"
  End With
End Sub