Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Scientific Notation convert to text

This is a discussion on Scientific Notation convert to text within the Excel Questions forums, part of the Question Forums category; If I receive an excel spreadsheet where certain job ids have been converted to scientifc notation, is there a way ...

  1. #1
    Board Regular
    Join Date
    Nov 2002
    Location
    Denver, CO
    Posts
    110

    Default Scientific Notation convert to text

    If I receive an excel spreadsheet where certain job ids have been converted to scientifc notation, is there a way to make them convert back to their original number? I understand that is I receive a .csv or .txt file I can import the field as "text" and this will not happen - but if someone has already brought it in and saved it this way as a .xls file is there a way to undo it (assume I have no recourse to the original .csv or .txt file). Thank you. Marie

  2. #2
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default Re: Scientific Notation convert to text

    Excel displays a maximum of 15 digits. You could try mucking around with custom number formatting. It may drop some numbers at the end.

    Try 000000000000000 or 0000000000000.00

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: Scientific Notation convert to text

    Do you have a finite set of values that created the scientific notation values? If so, can you list 'em here?

  4. #4
    Board Regular
    Join Date
    Nov 2002
    Location
    Denver, CO
    Posts
    110

    Default Re: Scientific Notation convert to text

    Thanks! I'll try the formatting route. But in answer to the "rules" - our job id's are always 7 digits with an alpha character in the 4th position. Those job id's with an "E" in the 4th position are the ones that come across as scientific notation. A couple of examples:

    321E067 becomes 3.21E+69
    123E123 becomes 1.23E+125

    Thanks again for looking at this. Marie

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: Scientific Notation convert to text

    Like this?

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    1
    3.21E+69321E067*****
    2
    1.23E+125123E123*****
    3
    666D003666D003*****
    4
    *******
    Sheet1*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: Scientific Notation convert to text

    This also seems to fire:

    =LEFT(TEXT(a1,"#"),3)&"E"&TEXT(LEN(TEXT(a1,"#"))-3,"0##")

    Pretty limited testing though...

  7. #7
    Board Regular
    Join Date
    Nov 2002
    Location
    Denver, CO
    Posts
    110

    Default Re: Scientific Notation convert to text

    Thank you both! Nates formula doesn't reconstruct the job id with the alpha character "E" in it, but I'm sure I can figure out how to add that as well as adding an if statement much like Mark's.

    Neither formula accomodates a job Id that starts with zero. For example:

    021E102 comes across as 2.10E+103 When I apply Mark's function, it returns 2.1E+103 - which is odd since the two parts being concatenated are 210E&103 - which excel has decided to place in scientific notation again.

    Anyway - the number of jobs starting with a zero is small - so this is workable for me. Thank you both for your time! I always learn something. Marie

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: Scientific Notation convert to text

    Hello Again Amileaux,

    Quote Originally Posted by Amileaux
    Thank you both! Nates formula doesn't reconstruct the job id with the alpha character "E" in it, but I'm sure I can figure out how to add that as well as adding an if statement much like Mark's.
    Hmmm, did you mean leave the string alone if it's not numeric? As the function should work on Strings with E. Yep, an if() function just about ought to do it. Think you'll want an if() function to test for your 0###, see the following:

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2000 : OS = Windows Windows 2000
    File Edit View Insert Options Tools Data Window Help About
    =

    A
    B
    C
    D
    1
    StringScientificOrignial*PostNew*Submission
    2
    321E0673.21E+69321E067321E067
    3
    123E1231.23E+125123E123123E123
    4
    021E1022.10E+103210E101021E102
    5
    021E0532.10E+54210E052021E053
    Sheet1*

    [HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Bon chance.

  9. #9
    Board Regular
    Join Date
    Nov 2002
    Location
    Denver, CO
    Posts
    110

    Default Re: Scientific Notation convert to text

    Nat - I'm sorry I mistyped your formula into my spreadsheet - You do add in the "E". I will relook at this and see how it works with my leading zero question. Thank you. Marie

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: Scientific Notation convert to text

    Hello Marie,

    Click on a cell in column D, this function deals with your original question and your modified quandary regarding leading 0s. You can copy the function and paste it into your formula bar.

    Hope this helps.

Page 1 of 2 12 LastLast

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
  •  


DMCA.com