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.
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.
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.
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,””””)”
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!