CODEWITHSUNDEEP
excelexcel-2007excel-formulahex
Span
Span

Reputation: 332

Excel 2007 Duplicate Check Wrongly Converting Hex as Exponent

I have a spreadsheet used to verify a long list of 8-digit hexadecimal numbers for duplicates.

It has two columns - one for the hexadecimal values and another where the following formula is used to check for duplicates (given that this second column is column B):

=COUNTIF($B:$B, B1)

This has worked fine for most numbers except for these values:

69000700 and 690007E2.

The first column is formatted as text, however it seems that the COUNTIF function is doing some kind of unwanted implicit cast of my hex value, and taking the second hex value as an exponent (which would make it the same as the first value).

It also doesn't seem to matter what format my hex column is - the COUNTIF function always interprets these values as numbers and therefore they appear as duplicates.

Is there a way to ensure the COUNTIF function takes these cell values as string parameters without doing an implicit cast?

Upvotes: 2

Views: 316

Answers (1)

Jüri Ruut
Jüri Ruut

Reputation: 2530

Maybe it's feasible to add an extra column with a formula

=CHAR(34) & B1 & CHAR(34)

copied down.

The formula encloses the text into quotes, and "690007E2" will no longer be interpreted as "69000700" (Excel 2003).

Upvotes: 1

Related Questions