31

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.

Improve this question
6
  • 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
    Commented Oct 1, 2014 at 15:06
  • 4
    Probably a duplicate of this: superuser.com/questions/397049/…
    – Nick Russo
    Commented Oct 1, 2014 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.
    – ChocolateBus
    Commented Oct 1, 2014 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
    Commented Oct 1, 2014 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.
    – ChocolateBus
    Commented Oct 1, 2014 at 16:42

5 Answers 5

Reset to default
7

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.

Improve this answer
3
  • 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.
    – ChocolateBus
    Commented Oct 2, 2014 at 14:08
  • @albert276 - you said "I am copying partial text from one cell". In that scenario it works.
    – Mike Honey
    Commented Oct 2, 2014 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.
    Commented Aug 21, 2015 at 17:46
38

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 to reset the default 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
Improve this answer
3
  • 7
    "5. Disable all delimiters", "6. Click Finish", "7. Paste data"
    – valid
    Commented Aug 4, 2017 at 12:48
  • 2
    This is correct and works.
    – Dylan Cross
    Commented Jul 2, 2018 at 18:52
  • You sir, are a genius. thank you
    – Ivan Nel
    Commented May 30 at 8:41
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.

Improve this answer
1
  • 1
    Thanks this fixed the issue I was having. Was driving me nuts!
    – Vnge
    Commented Jun 8, 2016 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.
Improve this answer
0
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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .