+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    CrystalJim
    Guest

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

    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,


    --
    Jim

  2. #2
    Gary''s Student
    Guest

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

    If you insert an apostrophe (single quote) before the customer account
    number, Excel will not perform any exponential conversions.
    --
    Gary's Student


    "CrystalJim" wrote:

    > 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,
    >
    >
    > --
    > Jim


  3. #3
    CrystalJim
    Guest

    RE: Random Conversion of alpha/numeric text with letter "e" to exp

    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.





    --
    CrystalJim


    "Gary''s Student" wrote:

    > If you insert an apostrophe (single quote) before the customer account
    > number, Excel will not perform any exponential conversions.
    > --
    > Gary's Student
    >
    >
    > "CrystalJim" wrote:
    >
    > > 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,
    > >
    > >
    > > --
    > > Jim


  4. #4
    Gary''s Student
    Guest

    RE: Random Conversion of alpha/numeric text with letter "e" to exp

    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.
    --
    Gary's Student


    "CrystalJim" wrote:

    > 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.
    >
    >
    >
    >
    >
    > --
    > CrystalJim
    >
    >
    > "Gary''s Student" wrote:
    >
    > > If you insert an apostrophe (single quote) before the customer account
    > > number, Excel will not perform any exponential conversions.
    > > --
    > > Gary's Student
    > >
    > >
    > > "CrystalJim" wrote:
    > >
    > > > 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,
    > > >
    > > >
    > > > --
    > > > Jim


  5. #5
    CrystalJim
    Guest

    RE: Random Conversion of alpha/numeric text with letter "e" to exp

    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


    --
    Jim


    "Gary''s Student" wrote:

    > 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.
    > --
    > Gary's Student
    >
    >
    > "CrystalJim" wrote:
    >
    > > 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.
    > >
    > >
    > >
    > >
    > >
    > > --
    > > CrystalJim
    > >
    > >
    > > "Gary''s Student" wrote:
    > >
    > > > If you insert an apostrophe (single quote) before the customer account
    > > > number, Excel will not perform any exponential conversions.
    > > > --
    > > > Gary's Student
    > > >
    > > >
    > > > "CrystalJim" wrote:
    > > >
    > > > > 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,
    > > > >
    > > > >
    > > > > --
    > > > > Jim


  6. #6
    Dave Peterson
    Guest

    Re: Random Conversion of alpha/numeric text with letter "e" to exp

    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 wrote:
    >
    > 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
    >
    > --
    > Jim
    >
    > "Gary''s Student" wrote:
    >
    > > 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.
    > > --
    > > Gary's Student
    > >
    > >
    > > "CrystalJim" wrote:
    > >
    > > > 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.
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > --
    > > > CrystalJim
    > > >
    > > >
    > > > "Gary''s Student" wrote:
    > > >
    > > > > If you insert an apostrophe (single quote) before the customer account
    > > > > number, Excel will not perform any exponential conversions.
    > > > > --
    > > > > Gary's Student
    > > > >
    > > > >
    > > > > "CrystalJim" wrote:
    > > > >
    > > > > > 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,
    > > > > >
    > > > > >
    > > > > > --
    > > > > > Jim


    --

    Dave Peterson

  7. #7
    joeu2004@hotmail.com
    Guest

    Re: Random Conversion of alpha/numeric text with letter "e" to exp

    CrystalJim wrote:
    > 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?


    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.

    -----
    [1] See the Help page "Excel specifications and limits" > "Calculations
    specifications".


  8. #8
    CrystalJim
    Guest

    Re: Random Conversion of alpha/numeric text with letter "e" to exp

    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


    "Dave Peterson" wrote:

    > 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 wrote:
    > >
    > > 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
    > >
    > > --
    > > Jim
    > >
    > > "Gary''s Student" wrote:
    > >
    > > > 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.
    > > > --
    > > > Gary's Student
    > > >
    > > >
    > > > "CrystalJim" wrote:
    > > >
    > > > > 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.
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > > --
    > > > > CrystalJim
    > > > >
    > > > >
    > > > > "Gary''s Student" wrote:
    > > > >
    > > > > > If you insert an apostrophe (single quote) before the customer account
    > > > > > number, Excel will not perform any exponential conversions.
    > > > > > --
    > > > > > Gary's Student
    > > > > >
    > > > > >
    > > > > > "CrystalJim" wrote:
    > > > > >
    > > > > > > 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,
    > > > > > >
    > > > > > >
    > > > > > > --
    > > > > > > Jim

    >
    > --
    >
    > Dave Peterson
    >


  9. #9
    CrystalJim
    Guest

    Re: Random Conversion of alpha/numeric text with letter "e" to exp

    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


    "joeu2004@hotmail.com" wrote:

    > CrystalJim wrote:
    > > 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?

    >
    > 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.
    >
    > -----
    > [1] See the Help page "Excel specifications and limits" > "Calculations
    > specifications".
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1