Professor Excel

Comments 26

  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,
      Henrik

  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.
    Thanks!

    • 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,
      Henrik

  5. Al

    THANK YOU THANK YOU THANK YOU!!!! IT WAS SOOOOOOOOOOOOO ANNOYING THAT THEY REMOVED THIS WIZARD!

  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,
      Henrik

  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.

  11. Liz

    With “Get & Transform” tools, after “From Text/CSV”, if the Delimiter option is not displayed and all data is in one column, click on Edit and on the Home Tab, select Split Column, select By Delimiter and select the appropriate delimiter there

  12. Chris Paul

    I have followed the instructions to enable the Text Wizard (Legacy): Options, Data, From Text (Legacy) (check the box) and From Access Legacy (Check the box).

    When I the select Data, Get Data, Legacy Wizards, I see only From Access (Legacy), and not Text.

    Suggestions?

    • Henrik Schiffner

      Sorry, I haven’t experienced this before. What happens if you only enable “From Text (Legacy)”?

  13. Dwayne Vance

    THanks. WE recently changed to Office 365 and notice we lost the wizard option our ADMINs almost blew a fuse!! Thanks a lot.

  14. Michael Stimac

    The advice on how to Restore the “Text Import Wizard” was PRICELESS.
    The version of this utility in the Office 365 version of Excel, on the other hand,
    is utterly useless.

    Thank you, Thank you, Thank you!

    • Henrik Schiffner

      Thanks for the feedback. Actually it took me some time as well to figure it out. Especially because I didn’t find much about it online.
      But to be fair: Microsoft highlighted this new “feature” the first time I used it with a help link. But I guess I’ve just clicked it away… 🙂

  15. Adam Birchfield

    This is so much easier. Why would they hide this. I wasted all day trying to do this with their new system and it took a few min with the text wizard.

  16. rspiet

    How do I import a number as text complete with leading zeros with Get and Transform? We have a 5 digit clock number. Clock numbers like 00016 or 01616 should import as is and not be imported as 16 or 1616?

  17. Tony Lau

    Thank you very much. This is just the information I have been searching for on re-enabling Import Wizard.

  18. AR

    The new import tool would work for me but is there really not a way to specify the text qualifiers? Maybe I’m missing it but if not this seems like a major oversight. I did find that you can go into the query editor, select each column individually, go to the Transform Ribbon | Extract | Text Between Delimiters then enter a ‘ in the start and end delimiter. That seems like a lot of work for a feature that was just one checkbox that used to be checked by default.

    -AR

    • Henrik Schiffner

      You could use the localisation option and therefore indirectly define the text qualifiers. Maybe you could add a step to search & replace the text qualifier?

Leave a comment