Paste Delimited

[Eric]

I am working with a large list of addresses that are all in one column. i.e.
/ 123 Fair Ave Atlanta GA 12345 / all in a single cell. I need to break
these out so they are like this / 123 Fair Ave / Atlanta / GA / 12345
(street/city/state/zip).

I have accomplished this in the past by clicking into a cell cutting out
"Atlanta GA 12345" and pasting in the next cell over, but when pasting excel
automatically split each piece of data separated by a space into a separate
cell. This worked perfectly, but I have tried to repeat this methodology
without success. Can't figure it out.

Any thoughts are greatly appreciated.

Thank you

 

[Bernard Liengme]

I had this parsing occur once and never worked out why.
However, you could do a cut and paste on all the cell
Then use Data | Text to Column on all the newly entered State| Abbr | Zip>
Since there should be single space between each time, specify Delimited by
Space
best wishes

 

[David Biddulph]

Go into Data/ Text to Columns, and deselect space as a delimiter. Cancel at
that stage, and you'll hopefully find that the behaviour of your Paste is
back to normal.

 

[Eric]

That worked perfectly. Glad you knew that, it was driving me crazy that I
couldn't figure it out.

Thank you.

 

[Bernard Liengme]

Tell us this step by step please as I cannot get Paste to parse.
best wishes

 

[Eric]

Thanks for your post. Take a look at David Biddulph's response. If you
select "Space" as a delimiter in the file you're working will, the paste
function will work with the delimited function. Odd that it works that way,
but sometimes useful.

Thanks

 

[Eric]

Within the workbook you are working with, and while the active cell has any
text in it, (1) go to Text to Columns (2) select delimited (3) next (4) check
the box for space (5) hit cancel

After you do this, excel should paste anything you copy or paste as space
delimited. To accomplish this you need to double click into the cell before
you copy or paste. Copying the entire cell will not work.

Hope this helps.

 

[Bernard Liengme]

Oh yes, David meant us to Select not Deselect Space in the text-to-column
dialog
Thank you!

 

[Gord Dibben]

And I just hate the fact that Excel saves that space de-limited option.

Sometimes I want to copy a paragraph from an external source and Excel
inconveniently splits it all up into columns.

e.g. . copy the above lines into Excel with the space de-limited set.

Just because I forgot to switch off the space de-limited........aarrrghhh


Gord