Text-to-Columns Stuck

  • Thread starter Thread starter Evan
  • Start date Start date
E

Evan

On pasting text, the Text to Columns operation happens
without being asked for. Apparently it sticks from a
previous operation. How can I shut it off, so I can
manipulate the data first and then choose among the
various options to get columns? The only way I can
prevent it now is to open another application.
 
AFAIK, there is no builtin Excel capabillity to do this. Here is a
macro that I use to deal with that issue.

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

Dim rngO As Range

Application.ScreenUpdating = False

On Error Resume Next

Set rngO = ActiveSheet.UsedRange.SpecialCells(xlBlanks).Cells(1)
If rngO Is Nothing Then Set rngO = ActiveSheet.Cells(1, 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
 
And if you're doing it manually, just choose a dummy cell and put some filler in
it.

Then Data|text to columns
choose delimited
but uncheck all the delimiters
and clean up that dummy cell.
 
Back
Top