Exponential Problems in Excel

Let's call this installment, "The Mysterious Case of the Vanishing Parts." (Read carefully -- that's paRts, not paNts.)

And before I begin this sad story, remember to add your Excel question in the comments for Friday's Excelerators Quiz post, so you have the chance to win a shiny new monitor.

Strange Formatting

Last Friday, I was working on a client's Excel file, revising some VBA code that splits a list of manufacturing parts into multiple columns, strips a couple of characters off the front of the part name, and copies the results to another column.

It seemed to be going well, until I got an email from my client, saying that some of the part numbers looked funny. He included a screenshot, and indeed, those part numbers did look odd. Here's an example, using some dummy data.

ScientificNotation01

Scientific Notation Formatting

"Aha!" I thought. (Yes, I actually talk to myself like that. ;-) ) Those parts were all numbers, so Excel just formatted them as Scientific Notation. I could simply format the column as General at the end of the macro, to make them look right.

Unfortunately, it wasn't that simple. When I clicked on one of the affected cells, the formula bar showed 220 as the actual part number. So, if I changed the formatting to General, 220 is the part number that would be copied to other cells, later in the macro.

ScientificNotation02

However, after a bit more investigation, I found that the original part number wasn't 220, it was 22E1. Close, but manufacturing might be adversely affected if Excel starts making up new part numbers!

Why the Part Number Was Changed

Because the original part number (22E1) started with numbers, followed by the letter E, then another number, Excel interpreted it as a number in Scientific Notation. It converted that number to Excel's style of Scientific Notation (exponential) formatting – 2.20E+02.

I'm sure Excel was trying to help, but that creates problems, just as it does when Excel changes 6-10 to a date for you, without asking.

The workaround to this unsolicited help is to force the data to be recognized as text, as Microsoft explains in its article: Text or number converted to unintended number format in Excel.

Fixing the Problem

In my client's macro, instead of formatting the parts column after copying the part names, I added an apostrophe at the start of each part name.  That left the "E" parts in their original format, and the problem was solved.

Here's the formula that is added in the VBA code:

.Range("D2?).Formula = "=IF($A2<>$A1,""'"" & $U2,"""")"

ScientificNotation03

Scientific Notation Explained

If you'd like to know how scientific notation works, in fairly simple terms, you can read this article: Scientific Notation

And for an even shorter and simpler description, here's a short video in which a math teacher explains scientific notation. And remember to do your homework!


_______________

5 comments to Exponential Problems in Excel

  • What a studly Math teacher. I know I'm getting old when the Math teacher looks too young to be one.

  • Really? I didn't notice, because I was concentrating on the chalk board. ;-)

  • Jenna

    I am trying to turn a data set that includes one set of very long numbers (transaction numbers for a retailer) into a pivot table. I format the column with the long numbers as text in the raw data sheet, then when I create the pivot table, those long numbers automatically turn to scientific notation and numbers that are unique on the raw data sheet are now grouped together. How can I avoid this?

  • Hugo

    Changing numeric cells to text format is not enough.
    You must re-apply the input addionally.
    You could copy the whole column and paste it again, specifying "text only".

    This problem is not linked to pivots, but overall to referencing auch cells.

  • Paul J.

    There's an easy way without using macros. Let's say we have the following scenario:

    - User has a large 5,000-row CSV file. Excel's importing of CSV is buggy, so we can't use the data->import from text solution. Which rules out being able to specify certain columns as TEXT instead of GENERAL during the import process (which will allow us to avoid the exponential numbers issue in the first place). Thus user is forced to doubleclick on the CSV file, while will allow Excel to load all the data correctly. Except that we have the exponential number problem occurring in one of the columns, let's say column X. Let's also assume we have a one-line header, i.e. line 1.

    1) rightclick on column Y, the neighbouring column to the right, and select Insert. This will cause column Y to be shifted right and become column Z, and we have a new column Y, which is blank.
    2) Skipping Y1 (since row #1 is the header row), go to Y2 and type the following, exactly:
    =""&+X2
    That is, "equals sign, two doublequotes, ampersand, plus sign, (cell number)".
    3) this formula will fix the exponent being displayed in X2 to the correct string in Y2. Yay.
    4) copy Y2, then select-paste it into Y3-Y5000, i.e. the rest of the column.

    We aren't done yet though - that just fixes the display, if someone copies cells from column Y and pastes elsewhere, it will only copy the formula. So we need to convert column Y from formula into text.

    5) select Y2 to Y5000, then rightclick anywhere on the highlighted cells - select Paste Options->Values (the icon which shows "123", it should be the second icon from the left, right after the icon of a clipboard). This will basically re-paste the cells in Y back on themselves, but convert from formula to text.
    6) We're done - now the user can simply copy and paste whichever cells they want from column Y.
    Column X is still there though, you can delete it if you want, or leave it as proof of Excel's screwup.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>