Split Text to Columns over 2 Sheets

Does this work?
Assuming data is on Sheet 1 and "|" is the delimiter


Isolate data up to the 200th occurrence of "|" character
=FIND(CHAR(127),SUBSTITUTE(A1,"|",CHAR(127),200))
Copy data to Sheet 2

Isolate remainder of the line
=RIGHT(A1,(LEN(A1)-FIND(CHAR(127),SUBSTITUTE(A1,"|",CHAR(127),200))))
Copy data to Sheet 3

Then use Text to Columns on Sheet 2 and Sheet 3

Via code:

Please Login or Register  to view this content.

Special-K - thank you for the formula. The second formula works fine. However when I copy the first formula in Sheet2 I get a circular reference message, when I add 'Sheet1!' before A1 in the formula I get numbers instead of the data needed.

shg- thanks for the code. When I run this code I get '#VALUE!' in the cells where the data was and the same in Sheet2 where the data should be, Sheet 3 remains blank completely. I also get 'Run time errror '1004': Application defined or object defined', and the following line is highlighted ;-

PHP Code: 

With .Cells(iRow).Resize(nRow) 


Also the data contains quite a few zeros which I need to keep, for example a reference number like '01234567' or some data contains multiple zeros but excel reduces this to one zero.

I need to be able to keep the data as it is, but when doing text to column it can take a long time going through each column and specifying whether it is text, date or general. This is made even more difficult as row vary in length and the data appears in different positions.

Thanks

My Evaluate approach won't work with long strings. If SpecialK doesn't sort the fomula approach, can you post a meaningful sample of data for test?

Try this:

Please Login or Register  to view this content.

Try this:

Please Login or Register  to view this content.