Scientific Notation convert to text

Amileaux

Board Regular
Joined
Nov 3, 2002
Messages
110
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 Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
 
Upvote 0
Do you have a finite set of values that created the scientific notation values? If so, can you list 'em here?
 
Upvote 0
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
 
Upvote 0
This also seems to fire:

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

Pretty limited testing though...
 
Upvote 0
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
 
Upvote 0
Hello Again Amileaux,

Amileaux said:
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:
Book1
ABCD
1StringScientificOrignialPostNewSubmission
2321E0673.21E+69321E067321E067
3123E1231.23E+125123E123123E123
4021E1022.10E+103210E101021E102
5021E0532.10E+54210E052021E053
Sheet1


Bon chance. :)
 
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top