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

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

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

I tried your second idea before I left my original post but it didn't work.

Thanks for that idea too.

J

"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

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

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

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

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

Using a formula like =a1*1 to test if a1 is numeric is not a good test.

Excel will try to convert anything that looks like a number to a number when it
multiplies.

If you want to test a value to see if it's a number, use =isnumber(a1).

If you want to test a value to see if it's text, then use =istext(a1).

If you preformat all your cells at Text (FIRST), then all the entries will be
treated as text--even if some look like dates.

If you don't want to do that, you can use a leading apostrophe in front of each
entry to make sure that excel treats it as text.

'this is a test
'11-01-2006
'1234
'=sum(a1:a10)

will all be treated as text.



jkiser wrote:

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


--

Dave Peterson

Either you're missing my point or I'm missing yours.

The fact is, I don't want the text 11-3031 to be treated as a number (or
date), I want it to be treated as the text that it is. However, I can't seem
to figure out a way to do that. Even adding an apostrophe won't do it for
that particular entry. But, it will for others that are very similar (like
11-1031).

I've figured out a work-around so I'll just move on.

Thanks anyway for trying.



"Dave Peterson" wrote:

Using a formula like =a1*1 to test if a1 is numeric is not a good test.

Excel will try to convert anything that looks like a number to a number when it
multiplies.

If you want to test a value to see if it's a number, use =isnumber(a1).

If you want to test a value to see if it's text, then use =istext(a1).

If you preformat all your cells at Text (FIRST), then all the entries will be
treated as text--even if some look like dates.

If you don't want to do that, you can use a leading apostrophe in front of each
entry to make sure that excel treats it as text.

'this is a test
'11-01-2006
'1234
'=sum(a1:a10)

will all be treated as text.



jkiser wrote:

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


--

Dave Peterson

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

Are you writing that typing the apostrophe didn't make the entry text?

Or are you writing that PREFORMATTING the cell as Text, then typing the value
didn't make the entry text.

Either of these ways has never failed for me.



jkiser wrote:

Either you're missing my point or I'm missing yours.

The fact is, I don't want the text 11-3031 to be treated as a number (or
date), I want it to be treated as the text that it is. However, I can't seem
to figure out a way to do that. Even adding an apostrophe won't do it for
that particular entry. But, it will for others that are very similar (like
11-1031).

I've figured out a work-around so I'll just move on.

Thanks anyway for trying.

"Dave Peterson" wrote:

Using a formula like =a1*1 to test if a1 is numeric is not a good test.

Excel will try to convert anything that looks like a number to a number when it
multiplies.

If you want to test a value to see if it's a number, use =isnumber(a1).

If you want to test a value to see if it's text, then use =istext(a1).

If you preformat all your cells at Text (FIRST), then all the entries will be
treated as text--even if some look like dates.

If you don't want to do that, you can use a leading apostrophe in front of each
entry to make sure that excel treats it as text.

'this is a test
'11-01-2006
'1234
'=sum(a1:a10)

will all be treated as text.



jkiser wrote:

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


--

Dave Peterson


--

Dave Peterson

jkiser" wrote in message
...
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.


But when would you ever be trying to maths on text?

--
Regards,


Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk