Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving as tab delimited or comma delimited | Excel Discussion (Misc queries) | |||
space delimited files now tab delimited | Excel Discussion (Misc queries) | |||
How can I convert tab delimited files to pipe delimited? | Excel Discussion (Misc queries) | |||
delimited copy and paste | Excel Discussion (Misc queries) | |||
Paste comma delimited text into a spread sheet | Excel Discussion (Misc queries) |