32

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.

CharlieRB
  • 23,021
  • 6
  • 60
  • 107
ChocolateBus
  • 454

5 Answers5

41

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
Michel de Ruiter
  • 1,308
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.

Brian Folan
  • 426
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.

Mike Honey
  • 2,632
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.
jxn
  • 101
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.

crihowzat
  • 1