Excel Automatically Converting Hex Numbers to Scientific

**ChicagoTRS** · 01-25-2005, 06:06 PM

I have a problem that I am looking for a little help on...

I have a CSV file (comma delimited) that I am opening in Excel. One of the columns contains 8 digit hexadecimal numbers. When I open this file in Excel any of the hex numbers that have a hex "E" in the number are being converted to scientific numbers.

Example: 00089E58 is being converted and displayed as 8.9E+59

I really need these numbers to just simply display as is, even as just text. I do not need Excel to recognize them as hexadecimal or numbers at all. I tried converting the column to text after the file was opened but the scientific notations remain.

This file has ~50000 rows with probably 5% being converted into this unusable scientific number. I do not want to manually update these numbers if possible. Any help would be greatly appreciated.

Excel 2003

**AlfD** · 01-25-2005, 06:14 PM

Hi!

Second time this evening this has come up. Maybe someday Microsoft will find a way of giving us the option of switching of the eager beaver which does this.

My comment earlier was:

If the cell is formatted beforehand as text it usually (I honestly can't say always for me) keeps its format. Reformatting after it has been "recognised" as a number in scientific form is not a goer.

Best of luck

Alf

**ChicagoTRS** · 01-25-2005, 06:22 PM

Thanks for the help...

The number is definitely correct in the csv file before I open in excel. If I open the file in notepad I can see the correct hex number...just no way to format the column to text before opening the document. Kind of surprising you can't turn off this automatic stuff or import as text or anything.

I have some other ideas that I am giving a go...going to try and just export this column of data from the database and then cut & paste into the already open correctly formatted excel spreadsheet.

Read some past posts on this subject and I am also going to try and replace the "E" with some other character and then open in Excel, convert the field to text and then find and replace and make the other character an E.

I have hope...

**AlfD** · 01-25-2005, 06:37 PM

Hi!

If you are able to import via the text import routines of Excel and then use Text to Data, you can use the bit where you can choose to require the field to be Text.

Have you tried putting 123X45 in a cell and then using Find/Replace to change the X to an E? Scientific, indeed!

Alf

**ChicagoTRS** · 01-25-2005, 06:42 PM

Just ran across the text import solution...while doing a search on previous answers to this same question.

I think this may work for me...I changed the file extension from csv to txt and used the text import wizard and changed the column from general to text and it is importing correctly.

(you are correct the find and replace does not work...scientific doh!)

Thanks again...

**Harry Colon** · 03-22-2013, 01:12 AM

I have excel 2007. . ..I have a CSV file (comma delimited) that I am opening in Excel. One of the columns contains 10 digit numbers. When I open this file in Excel, 10 characters and larger are being converted to scientific numbers. My solution turned out to be as follows; Select the entire column and select "format cells" from the menu ribbon "cells/format". Then select the "numbers" tab and select the category "numbers" in the drop down list. Leave the number of decimal places at 2. Hit "ok" and your numbers should be converted from scientific format to standard general number format with 2 decimal places showing like this "123010741255.00". Now you can use the same process to either remove the 2 decimals or convert to "text" as you wish.

**crewex** · 09-05-2014, 03:04 PM

This problem also occurs when you try to type the value into a field. It will change the number 7E80 to 7E+80.

If you type 0x7E80, instead of 7E80 you will get the correct result. Try to adapt this to your import.

**Barnapkin** · 12-01-2016, 01:00 PM

This doesn't work when you paste in a list of Hex values.
I have made some funky if structures, but there is always a combination that creates an exception.
The If statements start to get multi layered.
It's clunky, and I keep finding exceptions.
Every time there is a leading zero(s) or a Es in the value, things blow up.

Hard to believe one cannot turn off the scientific notation.

I have thousands of numbers in a list, so manually adding 0x is not an option.