Text to rows

[JMorrell]

I can perform a text to column, but is there a text to row function?

e.g., in cell a1 I have;
jane,joe,larry,moe
and in cell a2 I have;
red,blue,green,yellow

I want it to look like this;
cell data; cell data
a1 jane; b1 red
a2 joe; b2 blue
a3 larry; b3 green
a4 moe; b4 yellow

I could do it manually, but I have many hundreds of rows with 10 cells with
this data configuration. I also have to perform this task every 2 weeks.

tia,

 

[JLatham]

I can perform a text to column, but is there a text to row function?

e.g., in cell a1 I have;
jane,joe,larry,moe
and in cell a2 I have;
red,blue,green,yellow

I want it to look like this;
cell data; cell data
a1 jane; b1 red
a2 joe; b2 blue
a3 larry; b3 green
a4 moe; b4 yellow

I could do it manually, but I have many hundreds of rows with 10 cells with
this data configuration. I also have to perform this task every 2 weeks.

tia,

 

[JLatham]

This is not going to help a lot with hundreds of rows, as it's just as
'manual', but you can use
Edit | Copy
followed by
Edit | Paste Special and choose the [Transpose] option. This can be used to
turn a row into a column, or a column into a row.

A macro could probably be built to deal with it rather quickly. You said
there are 10 cells, do you mean 10 names in A1, 10 entries in A2, etc?

 

[Eduardo]

Hi JMorrell,
First select column A and do Text to columns, Delimited, next, choose Comma
and click next next. That will separate each component
Then select column A and do copy, then paste special transpose, that will
put all the names in column, do the same with the 2nd row for the colors and
paste it in column B. That will solve your problem

 

[JMorrell]

After I posted this question, I remembered the 'transpose' option in Paste
Special. But with the data I have, this just isn't an option. I'll try to
break it out before I bring it into Excel.

Thanks for your suggestions.
--
JMorrell

This is not going to help a lot with hundreds of rows, as it's just as
'manual', but you can use
Edit | Copy
followed by
Edit | Paste Special and choose the [Transpose] option. This can be used to
turn a row into a column, or a column into a row.

A macro could probably be built to deal with it rather quickly. You said
there are 10 cells, do you mean 10 names in A1, 10 entries in A2, etc?

I can perform a text to column, but is there a text to row function?

e.g., in cell a1 I have;
jane,joe,larry,moe
and in cell a2 I have;
red,blue,green,yellow

I want it to look like this;
cell data; cell data
a1 jane; b1 red
a2 joe; b2 blue
a3 larry; b3 green
a4 moe; b4 yellow

I could do it manually, but I have many hundreds of rows with 10 cells with
this data configuration. I also have to perform this task every 2 weeks.

tia,