Some one HELP!!!! Please

benyam

New Member
Joined
Sep 12, 2002
Messages
4
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,118,462
Messages
5,572,260
Members
412,451
Latest member
newbie22922792
Top