Pasting External Data Problem

[Ron Rosenfeld]

If I paste external data into a worksheet, let us say it is something from this
NG, it is usually a trivial task to parse it out across columns using either a
defined separator, or fooling with column widths, using the
Data/Text-To-Columns wizard.

However, once I have instructed the text-to-columns wizard to use a particular
separator, on any subsequent pastes it will again use that separator, even if
that is not appropriate, and not give me the opportunity to give new
instructions.

For a single row, this is not a problem as I can paste into the formula bar.
But for multi-row data, it is an issue.

How can I reset XL so it will not try to perform a text-to-columns process on
new pasted data?

Thanks.


--ron

 

[Dave Peterson]

Select an empty cell and put some junk (anything!)
Run Data|text to columns against this cell.
Choose Delimited
But uncheck everything.
and finish up.

That should stop excel from being so helpful the next time.

But sometimes this is a very nice feature.

 

[Dave Peterson]

You could also close excel and then restart it.

If I paste external data into a worksheet, let us say it is something from this
NG, it is usually a trivial task to parse it out across columns using either a
defined separator, or fooling with column widths, using the
Data/Text-To-Columns wizard.

However, once I have instructed the text-to-columns wizard to use a particular
separator, on any subsequent pastes it will again use that separator, even if
that is not appropriate, and not give me the opportunity to give new
instructions.

For a single row, this is not a problem as I can paste into the formula bar.
But for multi-row data, it is an issue.

How can I reset XL so it will not try to perform a text-to-columns process on
new pasted data?

Thanks.

--ron

 

[pfsardella]

Watch the word wrap. I use the following to deal with that annoyance.
It uses the first blank cell to insert text, resets the
text-to-columns, and clears that cell.


Sub FixTextToColumns()
'' 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


HTH
Paul