It launches in the background when you start your machine. It is without doubt the neatest piece of software I've installed in the last few years. I never even thought of using such a tool in Excel.Īfter a brief search, I found PureText.

My requirement fell outside of Excel – I wanted it to paste into WordPress, MS Word, into Google Mail messages etc. What you see next depends on the data type, here’s a typical text file import dialog.Last year, I was looking for a tool that could shortcut the 'paste as values' command. Start at Data | Get and Transform then choose the file to import. It’s also used extensively in our book Real Time Excel to import info like currency exchange rates, stock prices and latest weather data for Excel 2019 and Excel 2016. We’ve looked at PowerQuery before to do things like transposing a table columns and rows.

Substituting specific imported values to something else.

Forcing imported fields to a specific cell type.

There’s a lot possible in PowerQuery, we’ll just look at a few simple options to get you started. The same steps can be repeated and even copied to other conversions. The beauty of this feature is that once the initial import is done, PowerQuery lets you transform the information to suit your needs.Įach time the data is imported, the same transformations and changes can be done automatically. It can import a wide range of types from file or online sources. If you tried and gave up on PowerQuery, we strongly suggest giving it another go.

The original Get and Transform was limited and buggy. PowerQuery is an example of a feature that was dropped into Office before it was ready. Try Data | Insert for the limited options available.

It’s in Excel 365, 2019, 20 for Windows with features that have improved a lot over the years.Įxcel for Mac: sorry, there’s no sign of Get and Transform making it to Excel for Mac. The better option for importing any type of data, including CSV files, is Get and Transform or PowerQuery (Microsoft plays a bit with naming). However, that bypasses the Text Import Wizard and lets Excel play all merry hell with the original text. CSV files can be opened directly with just a double-click in Explorer because. That opens the text file with the Text Import Wizard which has some conversion options including forcing a field to text. The old way to import text is simply to File | Open and select the file. Text can be mistaken for dates or even large numbers. Numeric Identifying codes or SKU’s are converted to numbers. Modern Excel for Windows has a much better way to import text files and other data which will avoid or fix problems that the older Open file or Text Import Wizard options cause.Įxcel tries to help when opening text files like CSV or TSV by converting text into cell types like dates, numbers etc.

Excel text import wizard clipboard