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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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,209
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>
 

Forum statistics

Threads
1,147,515
Messages
5,741,615
Members
423,674
Latest member
Charles2dodo

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top