Delimiting when empty column exists.

[BPSJACK]

Hi,

I have a cell with data separated by hashtags and the letter 'x'

For example #1001x1 refers to a product code and x1 being the quantity.

#1002x2#1003x1# would become:

CODE	QTY	CODE	QTY
1002	2	1003	1

I'm currently using the code:

Please Login or Register  to view this content.

Sometimes there will be files when there will be lots of codes to delimit, but sometimes there will be no codes to delmit.

So when there reference will only be #1001x1# the above code will try and delimite the next column along (column G) but there is nothing there to delimit and the error code "No data was selected to parse" pops up.

What I'm trying to ask is, is there a way to make a macro that only delimit columns with data. Sorry if it's unclear, my knowledge is a little lacking in this subject and it's difficult to explain.

[x65140]

Can you post an example of it not working...i tried it and it works fine.
I used these codes - (with some blank cells inbetween)
#1002x2#1003x1#
#1002x2#1003

#1002x2#
#1002x2
#1002x2#1003x1

#1002
#1002x
#1002x2#1003x

[Vikas_Gautam]

I have a way around..

first You have to use split function.. using "#" as dilimiter it will result in an array..

then transpose this array ...

after this.. split each cell in that array using the "x" as delimiter..
and simulteneously populating a second array with values of code and quantity..



Don't forget to click *

[watersev]

hi BPSJACK, just a thought if I understood everything correctly:

Option #1

Manual mode:
1. Select D column, CTRL+F, find: x, replace: #
2. Selection, texttocolumns, delimiter: #

VB:

Please Login or Register  to view this content.

Option #2
In case I'm wrong or you want to stick with what you have for now.

Add this as the first line of the code:

Please Login or Register  to view this content.

This would cancel error message if any wants to appear

[BPSJACK]

Thanks guys, I'll give those solutions a try and report back.