text wrap delimeter

[armagan]

Hi,

I have a list which I am trying to sort. Some of the records contain 2
records in the same cell and have text wrap enabled (so one appears on
top of the other.

Is there a way to split them without manually editing each and every
cell?

I can't do a replace as there is no delimeter/separator (other than the
Alt-Enter used for text wrap)

ideally I need a routine which can be combined into a macro/VBA if
anyone can help.

cheers

 

[Guest]

Armagan, the ASCII character for Alt Enter is 10, therefore, you can separate
the two records in each cell.
In a blank column use the formula:
= LEFT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,CHAR(10),"*",LEN(A1)-LEN
(SUBSTITUTE(A1,CHAR(10),""))))) to get the first record.

In a second blank column use the formula:

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,CHAR(10),"*",LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))))) to get the second record.

You can then sort how you need. I don't know exactly how to put this in
VBA, but maybe someone in the discussion group can help with that. Hope this
helps you get started solving your problem.


abc
def

 

[armagan]

Cheers mate, that worked straight away

I'll try to find some way to work it into some VBA