Actually, a cell with a value of '11-3021 (preceded by an apostrophe) DOES
equal a cell with a text value of 11-3021 (without the apostrophe). A single
apostrophe simply indicates that the cell's value should be treated as text,
and the apostrophe is not part of the cell's value and will not affect
comparisons to other cells. To test this key the value of '11-3021 in cell A1
and enter the formula of =LEFT(A1,1) in another cell... no apostrophe will be
seen by the formula.

However, there does seem to be something funny going on with the formatting
in your sheet. I suggest that you select the column of data in question and
clear all formats with the menu option of Edit / Clear / Formats. This should
reveal the actual values in each cell, and may make it more evident what is
going on. Then change the cell formats to text (choose Text as the format
type in the Number page of the Format Cells dialog box), then type your
values back into the cell. This step is essential since changing the format
of a cell does not change its value, it only affects the way that future
entries are stored.

One more thought... if you are copying the data into this sheet make sure
that you use the 'Paste Special' option on the Edit menu, and select 'Values'
from the option box. That way only the values will be copied, and will not
override the formatting of the destination cells... this is important if you
want to assure that the copied data is not converted into numeric values
instead of text.

HTH,

TK

"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