Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: scientific number formats???

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i have a spreadsheet that has one of its columns listing various product part numbers. a few of those numbers are like this:
    10E0040
    10E0041
    etc, thru to 10E0049
    Excel keeps automatically making the value of these (even if i enter it as a number) as 1E+41, 1E+42, etc. if i multiply all by 1 to make them into a number, it makes it like 10000000000000000000000000, instead of reading what i type in. these part numbers need to read as numbers because my list is sorted by part number. some part numbers have letters in them, some don't. how do i turn this function off? i do not know code, so any help other than code will be greatly appreciated!!!

    TIA!

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Two choices. First Format Cells, Text or second start the part number with a single qoute like '10e1234 and Excel will leave things alone. Now the bad news the cells it has already converted will have to be redone by hand, unless someone else has a sudgestion.

    HTH,

    Rocky...

    [ This Message was edited by: Rocky E on 2002-04-28 00:05 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i just tried that, and it is still sorting them incorrectly because of it. for example, part number 10E0040 is coming before part number 08A0100. my formulas will not read correctly unless i can figure out a way to keep it from doing this. all of my formulas that will bounce off of this number are IF type formulas that have ranges as names. it stops searching for 08A0100 because it thinks it's not there because it sees the 10E0040. i'm half bald right now because it is driving me crazy!!

    Thanks for you help,

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,060
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    it makes it like 10000000000000000000000000, instead of reading what i type in.

    What number did you type in?

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    10E0040

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,060
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-04-28 00:12, celeste wrote:
    10E0040
    If you insist on entering numeric and non-numeric data in the same column, you could enter part numbers with this structure with a quote like this:

    '10E0040


  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Woah slow down, relax a little. Trust us Ecell does work, something is just confussing it a little. When I get this way here what I do. Take a new worksheet. Format the whole column "A" as text. The enter by hand at least 3 part numbers out of order. Select the cokumn and click A->Z or Data, sort. Look carefully at the numbers. Does everything look the same as yours? Must be something different as the does work on my machine. Ssometimes if we have 8000 row with 24 columns the problems are harder to find.

    This will work. A lot of us deal with part numbers everyday.

    HTH

    Rocky...

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i have them in as text now, so far there are only 10 that i have had to correct. i'm just worried about my formulas referencing off of them correctly. i'm going to double check them tomorrow.

    Thanks all for your help,

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Celeste:
    The use of letter e preceded by some numeric digits in a numeric expression is used as scientific notation for really large and really small numbers. So, it would be prudent to change from the letter e to any other letter in your part number -- I recognize it may not be possible. But sooner or later you are going to run into problems again, when an innocent looking part number may be coerced into being a number again.
    If you can change the letter e in the part number, you can do that easily by using the SUBSTITUTE command to another letter.

    Hope This Helps!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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