22

When I am copying partial text from one cell and pasting into another what I am pasting is separating into multiple cells.

Example:

Copying "June 15th" from a cell containing "Little Timmy's Birthday is June 15th", it pastes into two cells "June" and "15th".

Could there be a setting I have inadvertently changed? I have used the text to columns feature this morning splitting by space in another workbook, but I don't know why that may have carried over to when I use a simple Ctrl+C and Ctrl+V.

  • 5
    Using Data > Text to Columns does seem to change the behavior of copy-paste as you've described. Since there's no obvious way to change it back, I'm gonna call it a bug for the moment, though I'm sure someone thought it would be a nice feature. – Nick Russo Oct 1 '14 at 15:06
  • 4
    Probably a duplicate of this: superuser.com/questions/397049/… – Nick Russo Oct 1 '14 at 15:09
  • Yes, it looks like it was, that answer didn't come up when I searched. It appears to still be happening in excel 2013. – albert276 Oct 1 '14 at 15:27
  • 2
    I guess Text to Columns may always be enabled, and what's really happening is that the delimeter is being changed. So for instance, even without using the feature in a new workbook, if you paste something with tabs, it will span multiple cells. So the hidden setting you want to find is Text to Columns delimiter. – Nick Russo Oct 1 '14 at 15:31
  • I does appear to be the text to columns default was keeping the split by space option. I've never noticed that before. Thanks for your help. – albert276 Oct 1 '14 at 16:42
6

If you have a text string copied that you want to appear in a single cell, then select that cell and change to Edit mode, then paste your clipboard.

There are a few ways to enter Edit mode e.g. double click the cell, hit F2 key etc. It's described in detail here:

http://office.microsoft.com/en-au/excel-help/edit-cell-contents-HP001216389.aspx

Using that approach, there is no way for your paste to spread to another cell, regardless of Text to Columns settings.

  • That doesn't work, because if I copy a cell and then double click in a different cell where I want to paste, paste is no longer an option. it's far simpler surely to just change the settings of Text to Columns. – albert276 Oct 2 '14 at 14:08
  • @albert276 - you said "I am copying partial text from one cell". In that scenario it works. – Mike Honey Oct 2 '14 at 23:26
  • In my case, I would add another answer but this is a protected Question. I right clicked the sheet tab and selected "ungroup" my sheets were grouped together causing this undesired behavior – JZ. Aug 21 '15 at 17:46
20

As noted in the comments by Nick Russo, the delimiter(s) used for Data > Text to Columns seem to be applied to pasting data as well. At least since Excel 2007.

The way fix this behavior is:

  1. Select a non-empty cell
  2. Do Data -> Text to Columns
  3. Make sure to choose Delimited
  4. Click Next >
  5. Enable the Tab delimiter, disable all the others
  6. Clear Treat consecutive delimiters as one
  7. Click Cancel
  8. Now try pasting your data again
  • 3
    "5. Disable all delimiters", "6. Click Finish", "7. Paste data" – valid Aug 4 '17 at 12:48
  • This is correct and works. – Dylan Cross Jul 2 '18 at 18:52
10

Highlight the cells you want to paste into (before you paste into them); Do Text to Column; untick all the options (spaces specifically by the sound of your question). Then when you paste your data into those cells, they won't automatically break out.

  • 1
    Thanks this fixed the issue I was having. Was driving me nuts! – Vnge Jun 8 '16 at 15:45
0

I met with this problem today. Here is my solution:

  1. First select the rows you want to copy.
  2. Next, on the sheet you want to paste the copied cells, right click and select paste special.
  3. Select paste values and skip blanks. This should work. Just to make sure, if you copied 12 rows, make sure only 12 rows are pasted as well.
0

Paste your data, go select any column from the pasted data. Go to Data and text to column "delimited" click next under the delimiters remove the check from "space" and click finish.

Now copy the data again and paste it on a fresh tab. It should work fine.

protected by bwDraco Jul 17 '15 at 6:55

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.