Random Conversion of alpha/numeric text with letter "e" to exponen

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OS - w2003, office 2003, desktop XP Pro/ w2000 Pro

My company uses a six digit alpha - numeric string for customer account
numbers.

a few are all numeric, most are in the format: ##X###, where # = numeric and
X = an alpha character.

A few accounts, 101 out of 360, that contain an "e" as the alpha character
are converted to an exponential by Excel. the other 259 are not. These 101
accounts where the first accounts created using "e", the subsequent 259
accounts are not converted by Excel.

Anyone have a clue as to what is going on and how I can stop it?

Thanks,
 
If you insert an apostrophe (single quote) before the customer account
number, Excel will not perform any exponential conversions.
 
Understood. However, an apostrophe does not work in the lookup table. The
issue is, why does Excel treat these 101 values as exponential but not the
others? What makes them special to Excel?

All values come from a SQL Server database/Table column whose datatype is
nvarchar(40). Yet these and only these are somehow seen as different from the
others.

Also, it happens every time one of these 101 account numbers, is imported
into an Excel spreadsheet.
 
Excel thinks its "helping" you by performing the conversion.

The single quote should work in VLOOKUP().

I realize this may be distasteful, but the easiest solution may be to
re-define your customer ID's so they all start with a letter.
 
We tried the quote - it did not work.
Changing the account number is not an option.

Why does Excel only convert some number though? It should be converting all
of them, not just a select few. What makes those it does convert unique?
Whay does Exce see them differently?

Why does 10e305 convert and 10e307 not? (We don't have a 10e306.)

In fact all accounts 10e305 and lower convert, 10e307 and above do not. All
of the time.

The create dates are random, in that some created withing the same time span
convert and some don't (not sure why they are not created sequentially). I
do know that all of the converting dates where created before I started
working here, so my boss can't blame me:)
 
If you look at Excel's help for Specifications, you'll see this:

Feature Maximum limit

Largest number allowed to be typed into a cell 9.99999999999999E307

so 10e307 is too large and excel knows it isn't a number.

I'm not sure why the apostrophe didn't work. That makes it so excel will treat
your entry as text--not numbers.

If it's because you have a combination of text and numbers in the key column of
your lookup table, I would think it would be time well spent to make sure that
that lookup table column is nice--all text, no numbers.
 
CrystalJim said:
My company uses a six digit alpha - numeric string for customer account
numbers. a few are all numeric, most are in the format: ##X###, where
# = numeric and X = an alpha character.
[....]
The issue is, why does Excel treat these 101 values as exponential but
not the others? What makes them special to Excel?
Click to expand...

In Excel 2003 (at least), the largest number that can be typed is
roughly 9.9999e307 [1]. So any of your account numbers of the form
99e306 and "smaller" will be interpreted as a number entered in
scientific notation. I do not see any way to disable this automatic
interpretation other than by prefixing the data entry with an
apostrophe (') or by having the forethought to format the cell as Text
before entering the data.
 
Thanks, that makes sense about the number size. Couldn't see the forest,
damn trees got in the way.

And thanks for the suggestion about the lookup table, I will have my boss
send me the workbook with the lookup spreadsheet and see if that is the
problem.

Thanks Dave and Gary's student, for taking the time to help, appreciate it.

Jim
 
Thanks for responding, Never though about the upper limit on the number, made
perfect sense once I thought about it.

Now to see if we can find a permanent solution, like changing these 101
account numbers.

Thanks again,

Jim


[email protected] said:
CrystalJim said:
My company uses a six digit alpha - numeric string for customer account
numbers. a few are all numeric, most are in the format: ##X###, where
# = numeric and X = an alpha character.
[....]
The issue is, why does Excel treat these 101 values as exponential but
not the others? What makes them special to Excel?
Click to expand...

In Excel 2003 (at least), the largest number that can be typed is
roughly 9.9999e307 [1]. So any of your account numbers of the form
99e306 and "smaller" will be interpreted as a number entered in
scientific notation. I do not see any way to disable this automatic
interpretation other than by prefixing the data entry with an
apostrophe (') or by having the forethought to format the cell as Text
before entering the data.
Click to expand...
 
Back
Top