Home |
Search |
Today's Posts |
#1
|
|||
|
|||
EXCEL HEX2DEC conversion error
I read a file of hex numbers into Excel. I then paste the function HEX2DEC
into coulumn C. I select column A (where the file of hex numbers were entered) as the numbers to convert. The vast majority of the numbers are converted correctly, however hex numbers such as 34e3 are converted as if they are in sciencetic notation. example 1.00E+08 4294967296 WRONG should be 1e08 = 7688 1.00E+08 4294967296 00000010d8 4312 right 1.10E+07 285212672 2.00E+18 #NUM! 2.00E+10 #NUM! 2.00E+46 #NUM! 2.00E+46 #NUM! and I have seen 5dec be converted as a date! What am I doing wrong? How do I tell Excel that column A is strictly hex numbers? |
#2
|
|||
|
|||
I should have added that the file of hex numbers are not in sciencetific
notation. hmmm......means its the routine that reads the file into column A and not the HEX2DEC conversion routine. "Jerry" wrote: I read a file of hex numbers into Excel. I then paste the function HEX2DEC into coulumn C. I select column A (where the file of hex numbers were entered) as the numbers to convert. The vast majority of the numbers are converted correctly, however hex numbers such as 34e3 are converted as if they are in sciencetic notation. example 1.00E+08 4294967296 WRONG should be 1e08 = 7688 1.00E+08 4294967296 00000010d8 4312 right 1.10E+07 285212672 2.00E+18 #NUM! 2.00E+10 #NUM! 2.00E+46 #NUM! 2.00E+46 #NUM! and I have seen 5dec be converted as a date! What am I doing wrong? How do I tell Excel that column A is strictly hex numbers? |
#3
|
|||
|
|||
On Wed, 15 Jun 2005 07:01:09 -0700, "Jerry"
wrote: I read a file of hex numbers into Excel. I then paste the function HEX2DEC into coulumn C. I select column A (where the file of hex numbers were entered) as the numbers to convert. The vast majority of the numbers are converted correctly, however hex numbers such as 34e3 are converted as if they are in sciencetic notation. example 1.00E+08 4294967296 WRONG should be 1e08 = 7688 1.00E+08 4294967296 00000010d8 4312 right 1.10E+07 285212672 2.00E+18 #NUM! 2.00E+10 #NUM! 2.00E+46 #NUM! 2.00E+46 #NUM! and I have seen 5dec be converted as a date! What am I doing wrong? How do I tell Excel that column A is strictly hex numbers? You don't specify what you mean by "read a file of hex numbers into Excel" There are a variety of ways of doing this. For what you are doing, you need to "read" it in as TEXT. If you are opening a .txt file, this should bring up the data/text to columns wizard which has an option for importing the data as text. If you are opening a .csv file, you can rename it to a .txt file before opening it. If you are copying the data and then pasting it into Excel, try formatting the target as TEXT prior to pasting in the data. Post back with more information. --ron |
#4
|
|||
|
|||
The file is named whatever.hex.
I found that if I read it in as a text file, a few menus down in the OPEN file dialog box then all is well. Its a file of hex numbers I capture off a logic analyzer. Not having used Excel I'm a bit in the dark. I'll try renaming it as whatever.txt and see what happens. Thanks for the response. "Ron Rosenfeld" wrote: On Wed, 15 Jun 2005 07:01:09 -0700, "Jerry" wrote: I read a file of hex numbers into Excel. I then paste the function HEX2DEC into coulumn C. I select column A (where the file of hex numbers were entered) as the numbers to convert. The vast majority of the numbers are converted correctly, however hex numbers such as 34e3 are converted as if they are in sciencetic notation. example 1.00E+08 4294967296 WRONG should be 1e08 = 7688 1.00E+08 4294967296 00000010d8 4312 right 1.10E+07 285212672 2.00E+18 #NUM! 2.00E+10 #NUM! 2.00E+46 #NUM! 2.00E+46 #NUM! and I have seen 5dec be converted as a date! What am I doing wrong? How do I tell Excel that column A is strictly hex numbers? You don't specify what you mean by "read a file of hex numbers into Excel" There are a variety of ways of doing this. For what you are doing, you need to "read" it in as TEXT. If you are opening a .txt file, this should bring up the data/text to columns wizard which has an option for importing the data as text. If you are opening a .csv file, you can rename it to a .txt file before opening it. If you are copying the data and then pasting it into Excel, try formatting the target as TEXT prior to pasting in the data. Post back with more information. --ron |
#5
|
|||
|
|||
On Wed, 15 Jun 2005 14:31:06 -0700, "Jerry"
wrote: The file is named whatever.hex. I found that if I read it in as a text file, a few menus down in the OPEN file dialog box then all is well. Its a file of hex numbers I capture off a logic analyzer. Not having used Excel I'm a bit in the dark. I'll try renaming it as whatever.txt and see what happens. That "should" work; especially if there are no extraneous characters in the file. Excel has this sometimes annoying habit of trying to be helpful by translating things that look like numbers (even in scientific notation) into numbers; and things that look like dates, into dates. The only way I know of to circumvent that behavior is to convince Excel to treat the data as text. There are several ways of doing this -- we should be able to find a way that works for your. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cannot open excel file - error log created | Excel Discussion (Misc queries) | |||
when I try to download excel template, it says Error on page | Excel Discussion (Misc queries) | |||
Excel help giving runtime error | Excel Discussion (Misc queries) | |||
Error when entering and exiting excel | Excel Discussion (Misc queries) | |||
Downloading Templates for Excel error with a windows error. | Excel Discussion (Misc queries) |