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
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
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
Do you have a finite set of values that created the scientific notation values? If so, can you list 'em here?
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
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+69 321E067 2 1.23E+125 123E123 3 666D003 666D003 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.
This also seems to fire:
=LEFT(TEXT(a1,"#"),3)&"E"&TEXT(LEN(TEXT(a1,"#"))-3,"0##")
Pretty limited testing though...
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
Hello Again Amileaux,
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:Originally Posted by Amileaux
******** ******************** ************************************************************************>
Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows Windows 2000
File Edit View Insert Options Tools Data Window Help About
=
A B C D 1 String Scientific Orignial Post New Submission 2 321E067 3.21E+69 321E067 321E067 3 123E123 1.23E+125 123E123 123E123 4 021E102 2.10E+103 210E101 021E102 5 021E053 2.10E+54 210E052 021E053
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.
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
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.
Like this thread? Share it with others