Converting Imported data in Excel

I encountered an interesting problem with Excel this morning when I tried to import data copied from a table in a PDF file. The table was bad news from my accountant about my retirement investments. I won't be retiring any time soon ;)

The point of the issue however was that the data when copied and pasted into Excel came in neatly aligned in column but the numbers wouldn’t add up because they’d been added as text.

The solution to this is to use a feature previously called data parsing. What it does is to look at the data and convert it from one format to another. My only alternative would have been to select each cell, double-click on the cell to get the number on the screen, remove any characters that were causing issues such as any leading dollar signs or spaces and then press Enter to convert the text value into a number.

Luckily data parse does the work for you almost instantly. To do this, select the column of numbers that you’re working on. If you have a whole lot of columns to do, you still have to do each column one at a time. That’s the bad news; the rest of it is all good.

Select the column of numbers (if it includes some text entries that doesn’t matter), choose Data > Text to Columns and then select Delimited as the Original Data Type and click Next.

Click Next again and this is where you get to do the work. To convert text to numbers select the General option. If you have dates then select the Date option and select the date format that the data was created in. My values came from Australia so the date format used was dd-mmm-yy. Provided you select the date format that matches the dates you have, everything will convert just fine. Later if you want to show these in another format such as mm-dd-yy you do so using a date format. When you’re done, just click Finish.

The data will be instantly converted and you can move forward to do the same thing on the next column.

I estimate that this process took me about three minutes to do and on the data that I had it could have taken me half an hour or more to fix it all manually.

So next time you’ve imported data from an external source and when you need to convert text back into numbers check out the Data > Text in Columns option.