Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Text to Columns' problem (Excel 2000)-MULTI-'Text to Co

    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

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Text to Columns' problem (Excel 2000)-MULTI-'

    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
    Gre

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Text to Columns' problem (Excel 2000)-MULTI-'

    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. [img]/forums/images/smilies/sad.gif[/img]

    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

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Text to Columns' problem (Excel 2000)-MULTI-'

    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.
    Gre

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Text to Columns' problem (Excel 2000)-MULTI-'

    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

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Text to Columns' problem (Excel 2000)-MULTI-'Text to Co

    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.

    <pre>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
    </pre>


    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •