Convert multiple row to single rows

Hi,
I now have a row with 3 inputs, divided by the alt+enter function.
eg.
A1 contains
ABC
XYZ
JKL
and B1 contains a single value, say 10.

How do i convert it to A1 - ABC, B1 - 10.. A2 - XYZ, B2 - 10.. A3 - JKL, B3 - 10?

Thanks,
Rukia

Hi canapone,

What do you mean? There is no code.

In a single cell, i've 3 rows, separated by alt+enter. These 3 'rows' are indicated as 1 row, under A1.
Is it possible to split these 3 data up into A1, A2, and A3? Column B should have the same respective data.

Thanks,
Rukia

Hi Rukia (excuse my English),
I meant if the three strings housed in A1 ABC XYZ JKL have same lenght (3 letters each in the example): I'm sure you tried to use something like MID function nestled with CHAR(10) like I did - without success so far, as far as i concerned.

Freshman of the Forum

Hi Canapone,

Oh okay i get what you mean. Nope they are of different lengths. It can be any number of characters. Is there a way to split these 3 'rows' into 3 separate rows, and maintaining the value in column B for each of the row?

Any help is greatly appreciated!!

Rukia

Hi again,
maybe a little step forward (backward?) placing some formula below A1,

In A2 to obtain the first string
=LEFT(A1,FIND(CHAR(10),A1,1)-1)

In A3 the second string
=MID(A1;FIND(CHAR(10);A1;1)+1;(FIND(CHAR(10);MID(A1;FIND(CHAR(10);A1;1)+1;LEN(A1))))-1)

In A4
=MID(A1,LEN(A2)+LEN(A3)+3,LEN(A1)-LEN(A2)-LEN(A3))

Hope they work

Hi,
Thanks very much Canapone!