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.

Splitting data in Excel

Let's set the scene. You have a column in an Excel worksheet which contains a list of names, first name and last name all together. You want to split these into two columns, one for First name and one for last. How do you do this? Well one way is to use the Excel Text to Columns option.

Start by inserting a couple of columns to the right of the column which contains the names. Select the column containing the names and, in Excel 2007, click the Data tab on the Ribbon and choose Text to Columns. Step through the Wizard. In the first step choose Delimited and in Step 2 choose Space. Click Finish and answer Yes to the prompt to overwrite data. You will now have the data in two columns. You'll be able to remove the extra column you have there - it's best to add too many blank columns and later remove those you don't need because if you don't, you'll run the risk of losing the columns to the right of where you are working.

This is a simple and very fast solution for a name splitting problem.