text wrap delimeter
 

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


--
armagan
------------------------------------------------------------------------
armagan's Profile: http://www.excelforum.com/member.php...o&userid=34237
View this thread: http://www.excelforum.com/showthread...hreadid=539893

text wrap delimeter
 

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" wrote:


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


--
armagan
------------------------------------------------------------------------
armagan's Profile: http://www.excelforum.com/member.php...o&userid=34237
View this thread: http://www.excelforum.com/showthread...hreadid=539893

text wrap delimeter
 

Cheers mate, that worked straight away

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


--
armagan
------------------------------------------------------------------------
armagan's Profile: http://www.excelforum.com/member.php...o&userid=34237
View this thread: http://www.excelforum.com/showthread...hreadid=539893