On 2002-09-13 11:37, Bob Umlas wrote:

If the cell in question is A1:

in B1:

=LEFT(A1,FIND(CHAR(10),A1)-1)

in D1:

=MID(A1,FIND(CHAR(3),SUBSTITUTE(A1,CHAR(10),CHAR(3),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))))+1,255)

In C1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,B1,""),D1,""),CHAR(10),"")

However, if the info in A1 is something like

aaa

aaa

aaa then the result in C1 will be blank. With repetitive data in A1 you would need this formula (maybe it's shortenable!) instead:

=LEFT(MID(SUBSTITUTE(A1,CHAR(10),CHAR(3),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))),FIND(CHAR(10),SUBSTITUTE(A1,CHAR(10),CHAR(3),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))))+1,255),FIND(CHAR(3),MID(SUBSTITUTE(A1,CHAR(10),CHAR(3),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))),FIND(CHAR(10),SUBSTITUTE(A1,CHAR(10),CHAR(3),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))))+1,255))-1)

(I know, I know!)