Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    53
    Thanks
    2
    Thanked 1 Time in 1 Post

    Turning off the TextToColumns Wizard (Excel 2000+)

    The TextToColumns wizard is extremely helpful when I want to parse data pasted into a worksheet from (for example) an email, and I typically use it to parse on a space or a comma.

    However, there are times when I really do want to paste the whole text into a single column, regardless of its formatting. Is there some way I can "turn off" the wizard simply? I know I can do it by actually starting the wizard and changing the settings to a combination I know does not exist in the data I am about to paste (for example, to TAB), but that seems rather clunky, apart from being inefficient in use of my time and keystrokes. It also poses a (small) risk that it will actually change data on the worksheet if I happen to be on a cell with data that matches the criteria I select.

    I would prefer to do it through code, if possible, and suppose it is possible to execute the TextToColumn method to "prime" the wizard, but I am hoping there is a cleaner way that does not involve the potential of changing data.

    Thanks
    Geoffrey

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Turning off the TextToColumns Wizard (Excel 2000+)

    You can hold down shift when you open the file. This will prevent the wizard from activating!
    Regards,
    Rudi

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Turning off the TextToColumns Wizard (Excel 2000+)

    Try this macro! Not sure if its exactly what you need!
    Regards,
    Rudi

  4. #4
    Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    53
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Turning off the TextToColumns Wizard (Excel 20

    Thanks Rudi. Sorry if I did not make it clear that I am trying to avoid the Wizard when I paste text (Ctl/V), not when I am opening a file.
    Geoffrey

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Turning off the TextToColumns Wizard (Excel 20

    Does text-to-columns activate automatically when you paste text into a cell? ie, Do you copy and paste the text into a sheet, or are you importing data from a text file?
    Regards,
    Rudi

  6. #6
    Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    53
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Turning off the TextToColumns Wizard (Excel 20

    Does text-to-columns activate automatically when you paste text into a cell? Yes. That is what I want to disable, as stated in my first post.

    Do you copy and paste the text into a sheet, or are you importing data from a text file? As stated, I need to copy and past the data into a worksheet.

    Thanks.
    Geoffrey

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Turning off the TextToColumns Wizard (Excel 20

    In native Excel, the text-to-columns Wizard is not triggered by pasting data. If the data is numeric only, Excel will attempt to parse it into numbers in the most likely data formats (dates, percentages, currency, general numbers, etc.), but not if the source is mixed numbers and text. Can you provide an example of the text you are pasting, and describe exactly what happens?
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    53
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Turning off the TextToColumns Wizard (Excel 20

    The wizard does seem to kick in for me. Perhaps there is an option I can turn on or off.
    I am using Excel 2002 SP-2, but believe this happened when I was running earlier versions as well.
    The easiest way to explain it is to give a set of steps.
    1. Open Excel, create a workbook.
    2. Open Notepad, and enter the line "This is a line of text with 9 words" (no quotes) and a return.
    3. Select and copy the line (Ctl/C)
    4. Move to Excel and paste the clipboard into cell A1 (Ctl/V).
    5. The text is pasted into cell A1, as expected.
    6. Select cell A1
    7. Run the Text to Columns wizard, and select delimited. Alt/D-E-D
    8. Click Next and select "Space" only
    9. Click Finish. The wizard runs and splits the line into words in columns A:I
    10. Select cell C1 (for example). Repeat steps 2 and 3 if you have changed the clipboard for any reason.
    11. Paste the clipboard again (Ctl/V).
    12. On my system, the newly pasted data is now spread across columns A:I. That is what I mean when I say the wizard has run automatically, or by pasting the data.

    If you now run the wizard and change the delimiter to (for example) Tab, then pasting the data results in the whole sentence in one cell again.

    What I would like is to find some way to allow me to paste into a single cell without the parsing happening automatically.

    Thanks again.
    Geoffrey

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Turning off the TextToColumns Wizard (Excel 20

    The workaround you found seems to be what Microsoft recommends too - not what you wanted to hear, I fear:

    Unexpected Results Pasting Text After Using TextWizard

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Turning off the TextToColumns Wizard (Excel 20

    Could you not double click in the cell before pasting! IE Paste into a cell that is in edit mode. Doing this will paste the data explicitly into the cell. Does this not work. If this is the case, then it can also be automated as per your original post. I'll see if I can get you a workable solution based on your latest post!
    Regards,
    Rudi

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Turning off the TextToColumns Wizard (Excel 20

    This could give "strange results" if your text is more than 1 row: it will put all the text in one cell with carriage returns instead of multiple rows.

    Steve

Posting Permissions

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