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