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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,219,007
Messages
6,145,716
Members
450,636
Latest member
alanderson

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
Back
Top