Unwanted "text to columns" when pasting text

  • Thread starter Thread starter xirx
  • Start date Start date
X

xirx

Hi!

I double-click a source cell, select some text in it and copy it via CTRL-C.
Then I click a target cell and press CRTL-V to paste the selected text into
this cell. However, EXEL doese an unwanted "text to columns" here, thus
the text is not pasted into the target cell, but each word is pasted in a
seperate
cell.

Example:

Double-click cell A1, enter "A B" (A space B) in A. Press Return.
Double-click A1 again, select the "A B" and press CRTL-C.
Now click (once!) on cell B1, and press CRTL-V: In result, cell B1
contains the "A" while the "B" is in cell C1. But I want "A B" in
cell B1, of course (and cell C1 should not be modified).

As I don't see this behaviour all times, there seems to be a magical
switch to determine the CTRL-V behavior. But I can't find it.

Any pointers?

P.S.: A workaround is to double-click on the target-cell.
 
Hi!

I double-click a source cell, select some text in it and copy it via CTRL-C.
Then I click a target cell and press CRTL-V to paste the selected text into
this cell. However, EXEL doese an unwanted "text to columns" here, thus
the text is not pasted into the target cell, but each word is pasted in a
seperate
cell.

Example:

Double-click cell A1, enter "A B" (A space B) in A. Press Return.
Double-click A1 again, select the "A B" and press CRTL-C.
Now click (once!) on cell B1, and press CRTL-V: In result, cell B1
contains the "A" while the "B" is in cell C1. But I want "A B" in
cell B1, of course (and cell C1 should not be modified).

As I don't see this behaviour all times, there seems to be a magical
switch to determine the CTRL-V behavior. But I can't find it.

Any pointers?

P.S.: A workaround is to double-click on the target-cell.
Click to expand...

I believe the Text-to-Columns defaults to the last used behavior.

Another work around I've used is to select the cell, but then select the
formula bar and paste into that rather than into the cell.

Finally, here is a macro to reset that wizard. I cannot recall who contributed
it -- not original with me:

============================
Sub ResetTextToColumns()
'' Fixes Excel's memory when it parses pasted text automatically.

Dim rngO As Range

Application.ScreenUpdating = False

Set rngO = ActiveSheet.UsedRange.SpecialCells(xlBlanks).Cells(1)

With rngO
.Value = "Tim"
.TextToColumns Destination:=rngO, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1)
.ClearContents
End With

End Sub
=========================




--ron
 
If you don't want a macro solution, you can achieve the same thing by doing a
"dummy" text to columns.

Just choose one cell with something in it. (or put something in an empty cell)

Data|text to columns
choose delimited, but uncheck all the delimiters.
finish up and then erase the contents of that dummy cell (if you added it)
 
Back
Top