text across columns, or text across rows

markgriffith

New Member
Joined
Sep 1, 2002
Messages
2
Please forgive me if this is a stupidly simple question.

I want to paste large bodies of text either into columns or rows such that I get one word per cell. I see that there is an existing option under 'data' / 'text to columns' which allows this if I go through and click each proposed column-break manually, but I want this to be automated.

If I had a list like this:
1 house 2 tree 3 dog
I would like to either have a 3-cell row reading
<1 house> <2 tree> <3 dog> or a 3-cell column reading
<1 house> <2 tree> <3 dog>. Of course, the actual list will have hundreds of words in it, so I want to avoid manually marking the column/row breaks.

Is there an easy way to do this?

Many thanks! Mark Griffith
http://www.otherlanguages.org
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,934
Hi Mark and welcome to the board.

You could use Blank as delimitor for your Text to Columns, but then you'll get 1 house 2 trees etc. in 4 cells.

If your text cuold be seperated by comma for example like this: 1 house, 2 trees etc. then your delimiter would be comma.

If your text comes always as pair of words then you can concatenate afterwards.

Hope this helps a little.

Eli
 

markgriffith

New Member
Joined
Sep 1, 2002
Messages
2
Thanks Eli!
I think your space-as-delimitor suggestion cracks my problem.

I'll just go with the extra numbers-only columns - they're not hard to get rid of if I need to. 'Space-as-delimitor' is easy to work out?

I really appreciate the help!

Mark Griffith http://www.otherlanguages.org
 

Forum statistics

Threads
1,144,152
Messages
5,722,807
Members
422,459
Latest member
Chriselff

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