Skip to content

Solving Excel data conversion problems

When typing or importing data into Excel, the program tries to set the cell type (number, date etc) based on simple rules.    This is the default ‘General’ cell format at work.

But Excel’s guesses aren’t always right.  We humans insist on typing or importing data that doesn’t comply with Microsoft’s expectations.

To put it another way, sometimes Excel is too helpful for its own good.

The recent report from Mark Ziemann, Yotam Eren and Assam El-Osta in BioMed Central showed how Excel data conversion errors can catch even professionals.

In this article, we’ll describe some of the conversion problems to look for and how to avoid them.

Text or not?

If you type  ‘123’, Excel will make that a number.  But what if it’s text (like a product code) not a value?  You can strike problems if your column of text product codes has numbers in some cells.  In this example the problem is easy to spot because the text in column A is left aligned and the number in A4 is right-aligned.

If you see these mistakes while typing, that’s great, but they are easy to overlook.

If you have hundreds or thousands of rows (maybe imported from a text file or other source), the anomaly cells might not be obvious.

To fix, or just make sure, select the column and choose an explicit cell format.  In this case, Text.

Dates

Entering dates can cause all manner of trouble.  If you type or import  ‘3.2.2016’  is that 2nd February 2016 or 3rd March 2016?  That depends on the date format set in Windows matching the date format being typed or in the imported data.

Important: Excel converts dates based on the Windows setting for date formats under Control Panel | Regional Settings.  That means the data conversion behavior can and will be quite different between computers with the same version of Excel.

Type or import ‘NOVE2’ or ‘NOV2’ and Excel will turn that into a date ‘November 2002’.   ‘AP02’ is treated as text but ‘APR02’ is converted to ‘April 2002’.

In writing this article, we found another conversion anomaly.   Typing   ‘1/2’ meaning ‘half’ converted to the date 1 Feb 2016 because Excel used Peter’s Aussie date format (d/m/y) to wrongly guess that he meant 1st Feb.

To avoid mistakes, we always type dates with text for the month  ‘2 Feb 2016’ or ‘3 Mar 2016’ which Excel will convert to a date reliably.

In extreme cases, you may need to import the date field as plain text, then parse it into a separate Excel date field.  Use DateValue() plus some text functions to split the incoming date text into Day, Month and Year.

If the text has already been converted to a date, there’s no ‘Undo’ available.  You’ll have to find the original data and fix the cell manually.  Or re-import the entire data series again.

Numbers

Usually typing or importing number fields isn’t a problem.  Only numbers, the fullstop/period and thousands separator involved.  If almost anything else is in a number field it will be considered text.

However, as always, there’s an exception and the genetic studies issue found one.

Type in   ‘123E4’ and you’d expect it to be considered ‘General’ or text, but no.   Excel reads it as 1.23 million!   Why? Excel thinks it’s an exponential value ‘123 times 10 to the power 4’ or 1,230,000.

Think that can’t happen?  Think again.  Some of the genetic codes are in the format   ‘123456E45’.

Again, if the text has already been converted to a floating point number, there’s no ‘Undo’ available.  You’ll have to find the original data and fix the cell manually.  Or re-import the entire data series again.

Finding data errors

There’s a simple and fairly reliable way to check for data conversion mistakes in Excel.  Sort the columns.

Excel will sort the rows and anything in a differing cell format will be sorted to one or the other end of the list.  Sort the list both ascending and descending and see what appears at the top.  Here’s an example with a date and two numbers showing up at the top.

This trick doesn’t work as well for dates but is worth trying.

About this author

Office-Watch.com

Office Watch is the independent source of Microsoft Office news, tips and help since 1996. Don't miss our famous free newsletter.