Scientific Numbers (Text Format) Convert to actual number

[a_mahey]

Hello All,

Need some help please - I have a vast amount of numbers in a column which have been converted into a scientific notation i.e. 1.23E+10. The problem is that the notations are in text format, I need to convert them into the actual number i.e. without the 1.23E+10 and into the actual number. Can you please suggest a way? I am using Excel 2013

I have tried the normal convert the format to number etc but it doesnt work.

Thanks

 

[joeu2004]

If the cell displays 1.23E+10 and the format is text, you have probably lost the digits to the right. Even if you convert the text to a numeric value, the result will be 12300000000. I presume the original number and what you want is 12345678901. Right?

Can you go back to the original source of the data?

Is it a CSV file? Is it imported from an application (which?), for example Access? Is it something you exported from another website?

 

[a_mahey]

Sorry for the delayed response.

I cannot go back to the source system unfortunately. Yes it is a CSV file.
The file is provided from the source system and it isn't downloaded from a website. The number changes to scientific when the file is opened, which is required anyway as we have to amend columns in the file.

Please help.

Thanks

 

[Joe4]

One of my biggest pet peeves is that Microsoft determined that Excel should be the default program to open CSV files. This is a horrible idea, as when Excel opens a CSV normally, it sometimes does conversions on the data. So if you REALLY want to see what is contained in a CSV file, you should NOT use Excel to view it (at least not by opening it through normal methods in Excel). It is far better to use a Text Editor like NotePad, WordPad, or some other third party text editor program to view your CSV file.

So, your first step is to view the CSV file in a Text Editor program (before opening or editing in Excel) and confirm that the number really has all those digits, and it not in scientific notation.

If it looks good there, that is great, and we can work with it. The key is to open it in Excel using the following methodology:
1. Open a new blank file in Excel
2. Go to the Data menu, and then to the Get External Data ribbon
3. Select the "From Text" option
4. Browse to your file
5. This will invoke the Import Wizard
6. On Step 1, select "Delimited"
7. On Step 2, check the "Comma" box
8. On Step 3, select the field block where these number occur, and change the "Column data format" to "Text"
9. Click Finish, then OK

This will bring in the values as "Text", so you will have it converted to Scientific Notation.
This works because opening this method invokes the Data Import Wizard, which allows you to control the format of each data column (whereas, if you open directly in Excel the other way, Excel tries to guess at the format, and always chooses Number instead of Text).