Professor Excel

Comments 14

  1. Sander

    Very nice!

    We work with a system that spits out delimited text-files with Excel formulas.

    The good-old “Text Import Wizard” has a “default” type for columns. When using this type, Excel will interpret formulas in CSV/Text files. As far as I can see, this is not possible with “Get & Transform”. Therefore, to me, the old wizard is way more powerful than the new “Get & Transform” wizard.

    I hope Microsoft will restore the old behaviour in the new wizard before removing the old one permanently.

    If anyone has any ideas on how to import delimited text files containing formulas with the new wizard, I’m all ears.

    • Henrik Schiffner

      Hi Sander,
      Let’s hope, Microsoft doesn’t remove it permanently. But could you send me an example of your import file (just 2-3 rows are enough, please don’t send any confidential data) to me, so that I could try it myself?
      Thanks and best regards,

  2. Chuck

    Thank you so much! I didn’t know the wizard was going away. Good to know how to get it back when I lose it.

    I was looking for a way to change the default selections in the wizard. On the “Step 2 of 3”, delimiter is always Space, and I always, always, always use Comma. Text qualifier is always Quote, and I always, always, always use Apostrophe.

    Is there a way to make the wizard remember my selections from the last time I used it? Is there something I can twiddle in the registry to set “my” selections as the default?

    Thanks again!

  3. Chuck

    Dur! I just realized that I have lost the Text Import Wizard, but not the way I use it. This might be helpful to your readers.

    I use a SQL query tool and “copy rows.” This puts a CSV-like structure into my clipboard. When I have this “CSV” in the clipboard, Excel gives me the option Paste/Use Text Import Wizard.

    You could get this same access by, say, opening your CSV file in Notepad or something like it, select all text, copy, then go to Excel and use the good old Text Import Wizard.

    Again, thank you for the great information.

  4. Melissa

    Do you know how to bring back the legacy wizard on Excel for Mac? I’ve searched everywhere for similar options, but can’t find them. Logically, they’d be under Excel->Preferences, but I don’t see them there or elsewhere. I could be missing something though. The new version takes considerably longer with many more steps involved to do what I used to be able to do quickly with a couple of clicks. This might be the point where I dump Excel and try Numbers.

    • Henrik Schiffner

      Hi Melissa,
      I use Excel version 15.37 on Mac and I guess it’s the latest version right now. If you try to import a text file by going to Data –> From Text it still opens the original Text Import Wizard. Maybe that’s why you can’t find the legacy wizard because there is no new one on Mac?
      Best regards,

  5. Al


  6. Ron MVP

    Your intro should be tweaked. Instead of starting:
    “In Excel 2016 (since version 1704) …”
    it should read more like
    “In Excel 365 (only) 2016 (since version 1704) …”

    This “improvement’ is one of the “new features” limited to 365 subscriptions ONLY. This nuance of documentation is something that most people ignore. I have one-time payment Pro Plus, currently at Version 1707 Build 8326.2096 but I don’t have this new feature.

    This difference is precisely what I hate about this new marketing “scam” being used to “encourage” people to switch to the 365 subscription license scheme.

    I was trying to give someone help, but we were not working with the same user interface. I wasted an hour doing screen caps, only to have them find this article and point out my mistake.

    • Henrik Schiffner

      Hi Ron,
      Thanks for the correction. I’ve changed the introduction as you suggested.
      Best regards,

  7. Batu

    Thanks a lot.
    I couldnt seperate a txt file using two different seperater. For example i want to use “/” as seperator for date and “,” for rest of data. Txt import wizard is allowed this easily but “Get & Transform” tool not or i cant.
    Thanks again

  8. Jack

    In the legacy wizard we had the option to overwrite data. Is there a way to do this with the new tool?
    We also had the option to not have the data imported as a table but as a range.
    I can not find a way to do this with the new tool and it causes excel t o create new columns to the left preventing the overwrite. I could change to a range after the import but them have to cut and paste to relocate to proper position. I then have to delete the cells created by the import porcess.
    Any ideas?

  9. Shivani

    Thank you so much sir. Your article was of great help. Couldn’t have solved this problem without you. Much appreciated.

  10. Michael

    My import text and csv files with the “Get & Transform” tool does not show the drop down lists as per your example step 2. Really threw me for a loop. Is there a setting I need to turn on to see these? I am trying to import a text filee that has several spaces between the data items. The fixed width approach works great in the old wizard but I cannot figure out how to this with the new tools.

Leave a comment