hex number recognition

[trsnider]

Is there any way to get Excel 2007 to recognize a hex number using the 0x... format? Hex numbers in quotes "deadface" seems really useless.

 

[Gary McMaster]

The "Analysis Toolpack" (addin) has functions that recognize Hex, Octal and Binary.

Gary

 

[trsnider]

Thanks - I had read that in other searches prior to posting.
Under data -> data analysis I don't see any conversion related macros.
Is there another place I should be looking (brain dead today).

 

[shg]

=Hex2Dec("ABCD") returns 43981.

if you're using Excel 2003- and get a #NAME? error, Tools > Addins, tick Analysis Toolpak.

 

[trsnider]

I'll be more specific I'm using a file that has hex numbers in the form 0x...
Optimally I'd like to be able to use them 'as is' in functions (min, max, average...) without manipulating them just to satisfy excel's quirkyness.
That apparently isn't possible -- even with the analysis pack loaded -- (no applicable functions are seen in it).

So I can edit the file outside of excel & remove '0x's.
But when I copy the column in excel thinks it's smarter than me and converts numbers with an embedded '0e' to scientific notation.

Workarounds:
a: format the column as text -
remove the 0x in an editor, copy the column from an editor - paste it in using either (ctl+c or paste special 'text') -- b: changing column formats between general and text don't seem to help.
c: using text to columns specifying the format as text or general doesn't help.

the files are huge 500,000+ lines so it's impractical to go thru by hand and correct each one.

 

[xenou]

Optimally I'd like to be able to use them 'as is' in functions (min, max, average...) without manipulating them just to satisfy excel's quirkyness.

What spreadsheet application does not have this so-called quirkiness? As far as I know Excel, OOorg, LibreOffice, Gnumeric all do not natively recognize Hex data in cells.

It may be simplest to convert the whole column to DEC all at once and get with life. If you need Hex back again for output, convert back to Hex.

 

[shg]

Ditto.

A macro to convert a column of values from hex to decimal or vice versa would only require a few lines of code and a few seconds for a half million cells.