#### benyam

##### New Member
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

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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!)

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

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>

Replies
2
Views
136
Replies
3
Views
392
Replies
2
Views
114
Replies
2
Views
325
Replies
6
Views
199

1,219,959
Messages
6,151,154
Members
451,011
Latest member
Pigdog89

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back