Paste Delimited
 

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

Paste Delimited
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Eric" wrote in message
...
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

Paste Delimited
 

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.
--
David Biddulph

"Eric" wrote in message
...
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

Paste Delimited
 

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

Thank you.

"David Biddulph" wrote:

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.
--
David Biddulph

"Eric" wrote in message
...
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

Paste Delimited
 

Tell us this step by step please as I cannot get Paste to parse.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

"Eric" wrote in message
...
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

Paste Delimited
 

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Eric" wrote in message
...
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

Paste Delimited
 

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" wrote:

Tell us this step by step please as I cannot get Paste to parse.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

"Eric" wrote in message
...
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

Paste Delimited
 

Oh yes, David meant us to Select not Deselect Space in the text-to-column
dialog
Thank you!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Eric" wrote in message
...
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" wrote:

Tell us this step by step please as I cannot get Paste to parse.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

"Eric" wrote in message
...
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

Paste Delimited
 

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



On Wed, 5 Nov 2008 16:16:02 -0400, "Bernard Liengme"
wrote:

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