Exactly....that's the problem.

Excel is recognizing some text values as dates when I use them in a formula.
The problem is I don't want it to recognize text as a value, I want it to
recognize the text as text. As you've pointed out, it works for some
numbers, but not for others...so it seems quirky.

In thinking through this though, I'm guessing that Excel, when subjecting
text to a formula, trys to convert all text in this format to numbers. Thus,
in your example, it tries to convert all results to a date. But there are
some numbers for which there isn't a valid date so I get the #VALUE! error.

It seems to me there should be a way to tell Excel that you want to
interpret all of the entries as text (like by adding an apostrophe) even when
its in a formula but perhaps I'm missing something.

Thanks

J

"Sandy Mann" wrote:

jkiser,

It depends on whether or not XL can interpret the text as a date ot not.

Try this:

In C1 enter 11-1000 and in D1 =C1*1 you will get #VALUE! as before

Now highlight both cells and drag down past row 900. Why 900? because the
#VALUE! will stop there and you will get numbers in column D

Now highlight column D and custom format as:

d-mmm-yyyy (or mmm-d-yyyy)

you will see that the 11-1900 becomes 1-Nov-1900 (or Nov-1-1900)


--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"jkiser" wrote in message
...
None of these steps worked. Let's simplify this and see if you can
repeat
this where you are. Please try the following:

- Open a new spreadsheet
- Highlight the entire sheet and Edit/Clear/Formats
- Highlight column A and Format/Cells/Text
- Highlight column B and Format/Cells/Number (0 decimal places)

Next:
- In cells A1 and A2, type in the text 11-1021 and '11-1021
- In cells A3 and A4, type in the text 11-3021 and '11-3021
note the use of the apostrophe in cells A2 and A4

Then:
- In cell B1 enter the formula =A1*1
- Copy the formula to cells B2, B3 and B4

When I do this, I get the following results in cells B1 - B4

B1 = #VALUE!
B2 = #VALUE!
B3 = 409743
B4 = 409743

The #VALUE! in B1 and B2 are what I expect (and want). The numbers in B3
and B4 are the ones acting weird.

I'd appreciate it if you can see if this is replicated on your machine.

Thanks

J

"T Kirtley" wrote:

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