Text to rows

J

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

JLatham

JMorrell said:
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,
 
J

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?
 
E

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
 
J

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


JLatham said:
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?


JMorrell said:
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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top