‘Text to Columns’ problem (Excel 2000)-MULTI-‘Text to Co

Topic

New Reply

I have been using this function to parse the data in cells successfully. Unfortunately the wizard responsible seems to be one of those “TV comedy” wizards that can’t remember how to undo the spell.

Consequently, any data I subsequently paste is automatically parsed across numerous cells. The only way I can find to reverse the wizard’s spell is to shut down and restart Excel (just the workbook is insufficient). Does anybody know of a “After you’ve opened the pod door, you can turn off the parsing too, HAL” command to rectify this?

thanks

Alan

Reply | Quote

Replies

From what you say, it would appear that you can’t access the code of this wizard. Do you have the same results if you choose Edit|Paste Special|Values? IF that does deactivate the wizard, you may be able to go back and do a normal Ctrl + V paste. HTH

Reply | Quote

I’ve tried Paste Special as plain or unicode text, but the result is the same. I’ve also tried resetting the wizard, by several means, but nothing seems to turn him off.

Guess I’ll try to work out some code to parse the data myself, since it’s always of the same general “nature”.

thanks

Alan

Reply | Quote

You should be able to find quite a number of different examples on the Lounge of the formulas you’ll need. Post back if you get stuck.

Reply | Quote

It’s largely a matter of plucking out any numeric values from a cell and then “scattering” them across the next empty cells in the row. I think I can work out some VBA to pick up on the digits/decimal point in the string and reconstitute the numbers into separate cells.

thanks

Alan

Reply | Quote

Alan,

I’ve been experiencing the same difficultly when pasting from other applications. If I’ve used the TextToColumns wizard and identified a delimiter, any subsequent pastes continue to use it as a delimiter.

Here’s a little macro that I wrote to “reset” the delimiters to nulls. Just attach it to a button, select the cell you want to paste to, run the macro, then paste away.

Sub ResetTextPaste()
'
    Dim strAddress As String
    ActiveCell.Value = " "
    strAddress = ActiveCell.Address
    Selection.TextToColumns Destination:=Range(strAddress), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1)
    ActiveCell.Value = ""
End Sub

For those who may be wondering what this is about, this is for when the Paste|Special option gives you these choices: BIFF format, SYLK format, HTML format, Unicode Format or Text format…and you want either Unicode Text or Text Format.

Reply | Quote