Scientific Notation convert to text

Thanks:  0
Likes:  0

# Thread: Scientific Notation convert to text

1. ## 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. ## 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. ## 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. ## 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. ## 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
 B1B2B3 =

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. ## 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. ## 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. ## Re: Scientific Notation convert to text

Hello Again Amileaux,

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
 C2D2C3D3C4D4C5D5 =

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. ## 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. ## 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.

## User Tag List

#### Posting Permissions

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