Some one HELP!!!! Please

[benyam]

Can some one tell me how to split a cell that has 3 lines in it into 3 different cells
for example
i have aaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaa

in one line i want split that in to 3 differnt cell so each line will be in one cell


Thank you
This message was edited by benyam on 2002-09-13 11:21
This message was edited by benyam on 2002-09-13 11:36

 

[BobUmlas]

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!)

 

[Aladin Akyurek]

If you have installed the add-in morefunc.xll,

in B1 enter:

=WMID(A1,1,1,CHAR(10))

in C1 enter:

=WMID(A1,2,1,CHAR(10))

in D1 enter:

=WMID(A1,3,1,CHAR(10))

 

[Jay Petrulis]

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!)

Hi,

Bob -- Wow! That is one brute of a formula. How did you manage to keep track of it all? That's quite a number of intermediate formulas to join. My head is spinning trying to decipher it. Good one.

I think the C1 formula can be shortened considerably with
=MID(A1,FIND(CHAR(10),A1)+1,FIND(CHAR(10),A1,FIND(CHAR(10),A1)+1)-FIND(CHAR(10),A1)-1)

it may not work in all conditions, though.

Aladin's suggestion to use the Laurent Longre add-in will likely be the preferred route, but if you are willing to preselect the correct number of cells, here are two UDFs that should do the job -- the H and V endings designate Horizontal and Vertical, which is the worksheet array that must be used.

<pre>Function ParseReturnsV(RefCell As Range)
ParseReturnsV = WorksheetFunction.Transpose(Split(RefCell, Chr(10)))
End Function

Function ParseReturnsH(RefCell As Range)
ParseReturnsH = Split(RefCell, Chr(10))
End Function</pre>