Can you enter the data as pure numbers (no dashes) and use a custom number
format of:
00-0000
?

A single apostrophe will tell excel to treat the data as text (kind of the same
as pre-formatting the cell as Text, then entering the value).

But I guess the question is what are you matching?

I've always found that life gets much easier if my value-to-match is the same as
the table-to-match.

Either all text or all numbers.

And if you put =istext(a1), =istext(a2), ..., =istext(a6), you'll find that the
rows with errors are already text. So starting with an apostrophe (or
preformatting the cell as text) may be ok.



jkiser wrote:

I tried that but it doesn't solve the problem.

I can accomplish what you're talking about by putting 2 apostrophes (or some
other character) in there but that renders the value unable to be tested
against another.

For instance '11-3021 will never equal 11-3021 when tested.

Thanks for trying.

J

"T Kirtley" wrote:

Excel is interpreting the values in rows 2 - 5 as dates since the structure
of the text looks like a MM-YYYY date value. The first and last rows don't do
this because they dont resolve to a date value that Excel suppports.

To fix this you could precede the text with an apostrophe, or format the
column as text and then re-enter the text strings.

HTH,

TK
"jkiser" wrote:

I've got some text entries in Column A1 through A6 as follows:

11-1021
11-3021
11-3051
11-9021
11-9041
13-1073

I've written a short test formula in cells B1 through B6 as follows:

=A1*1
=A2*1
=A3*1
=A4*1
=A5*1
=A6*1

The formula results in cells B1 through B6 as follows:

#VALUE! (as expected)
409743
420700
2601198
2608503
#VALUE! (as expected)

The results in cells B1 and B6 are as I expect, but I can't get rid of the
numbers in cells B2 through B5.

It appears the entries in cells A2 though A5 are behaving like numbers. For
instance, if I change the entry in A3 to 11-3021 the formula result changes
to 409743. I've checked and double-checked the formatting.

Any idea what's going on?

Thanks


--

Dave Peterson