Major bug in Excel

[kirkm]

I refer to a leading apostrophe in a text (or possibly any) cell. In
order to have one, you need two. Yes I know it's crazy but it's true!

There's no way to fix this, I suppose ?

Thanks - Kirk

 

[Gary''s Student]

Not a bug, not any type of insect.

That is the way Excel works.

 

[Mike H]

Hi,

It's not broken so it doesn't need fixing. The first apostrophe allows you
to display input as entered; leading zeroes for example, without having to
format as text and isn't displayed in the cell which is a very useful piece
of functionality.

It does have the side effect of you having to enter 2 apostrophes if you
want to see 1 but most would accept this is no big deal.

Mike

 

[Niek Otten]

One simple Google search for apostrophe and excel would have taught you that
this is not a bug, but a "feature".

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

in message news:[email protected]...

 

[kirkm]

I did Google and found quite a bit - including many attempts
to defend or justify it. It's not a 'feature' in my opinion but rather
a stupid mistake in design concept. Who in their right mind would
think to have 2 of something to get one. How ridiuculous - when you
set a cell to Text to have this limitation. An apostrophe *is* a text
character.
If you're using Excel as a plain table to display text, you're forced
to treat every leading apostrophe as a special case. This prevents
you automating an input routine. OK it's get-roundable, but messy.

The fx 'Edit' Window also displays this extra character, making it
inconsistent with everything else.

If - as Mike says - it helps input leading zero's as text, then it
should be an option to have it on or off. I was hoping such an option
might exist.

My question to the group was just to see if others found it equally
annoying and stupid, and maybe had devised a method (or hack) to avoid
it.

 

[Jon Peltier]

Before you go blaming stupid designers for designing something that you just
don't understand, you might be interested in where this comes from. Back in
ancient times, Lotus 1-2-3 was the premier spreadsheet program. For certain
formatting, Lotus allowed users to enter a single character prefix. The
character did not appear when the cell wasn't being edited, but remained to
tell the computer how to format the contents of the cell. The formatting
included such things as left, center, and right alignment of the cell. As
far as I know, the apostrophe is the only such character still in common
use. I don't know whether the others have been deprecated.

- Jon

 

[kirkm]

Before you go blaming stupid designers for designing something that you just
don't understand, you might be interested in where this comes from. Back in
ancient times, Lotus 1-2-3 was the premier spreadsheet program. For certain
formatting, Lotus allowed users to enter a single character prefix. The
character did not appear when the cell wasn't being edited, but remained to
tell the computer how to format the contents of the cell. The formatting
included such things as left, center, and right alignment of the cell. As
far as I know, the apostrophe is the only such character still in common
use. I don't know whether the others have been deprecated.

Jon, I do understand control characters inside text, and the usage
thereof.
The way Excel use this may be for backwards capabilty - and may even
be beneficiall when you want it. BUT if you don't want it, you should
be able to turn it off.

But there's more - it CAN be done! I've seen it. One leading
apostrophe in the cell, with the same text showing in the Fx edit line
(what's its name?), i.e. still showing just one apostrophe.

Isn't that somewhat inconsistent?

Cheers - kirk

 

[Nigel]

I agree that it is not very sensible and would have been better to 'design'
it out, from newer versions. But your assertion that it is a 'Major Bug in
Excel' may grab headlines and get people reading your post but hardly true.
IMO

--

Regards,
Nigel
(e-mail address removed)

 

[kirkm]

I agree that it is not very sensible and would have been better to 'design'
it out, from newer versions. But your assertion that it is a 'Major Bug in
Excel' may grab headlines and get people reading your post but hardly true.
IMO

You're right. 'Minor Bug' would have been better but you know how it
is - some days you hate Excel more than others...

If I find a fix/solution or reason for the inconsistency I'll post it.

Thanks to everyone for their comments.
Cheers - Kirk

 

[Jon Peltier]

Have you only seen it done (one apostrophe at the start of the cell, both in
the cell and in the formula bar), or have you actually done it and can
reproduce it? I do know how to do this, I just figured it out, but it's
obscure, and it's fragile and will revert to form when the cell is edited.

There are so many real bugs in Excel, that it's not worthwhile getting all
excited about a behavior which was designed that way on purpose, and is well
known and well documented.

- Jon

 

[kirkm]

On Mon, 26 Jan 2009 12:56:17 -0500, "Jon Peltier"

Hi Jon,

Have you only seen it done (one apostrophe at the start of the cell, both in
the cell and in the formula bar), or have you actually done it and can
reproduce it?

I can't do it. I have examples of it but can't reproduce it. Which is
very fraustrating... something is possible, can be seen to happen, but
can't be done at will.

I do know how to do this, I just figured it out, but it's
obscure, and it's fragile and will revert to form when the cell is edited.

Please tell all !

There are so many real bugs in Excel, that it's not worthwhile getting all
excited about a behavior which was designed that way on purpose, and is well
known and well documented.

Yes, it seems well accepted as a 'feature'. A text character which, on
it's own, behaves very differently to any other. Suppose we should all
be grateful it wasn't something more commnonly used.

Cheers - Kirk

 

[Jon Peltier]

I'm not sharing the trick because it is not robust. Every time it reverts,
someone will post another bug report.

Here's another text character which works differently from all others in an
Excel cell:

=

Like the apostrophe, its use in this situation is well documented, and it's
very commonly used. No danger that anyone will suspect it's a bug.

- Jon

 

[Nigel]

=(

That sequence might make them feel unhappy though! LOL

--

Regards,
Nigel
(e-mail address removed)

 

[kirkm]

I'm not sharing the trick because it is not robust.

That's a pity but I'm sure someone else will, or I'll figure it out. I
wonder if there's more than one way?

Here's another text character which works differently from all others in an
Excel cell:

=

'=' seems to works ok for me.
' -' goes beserk if you enter and click, but is fine if terminating
the entry with the Enter key.

 

[Jon Peltier]

That's a pity but I'm sure someone else will, or I'll figure it out. I
wonder if there's more than one way?

Why do you insist that you want to do something that works for a second if
you're lucky, but breaks so easily?

'=' seems to works ok for me.
' -' goes beserk if you enter and click, but is fine if terminating
the entry with the Enter key.

= works differently by creating a formula. So do + and - under some
circumstances for compatibility with Lotus 1-2-3, though Excel then inserts
= at the beginning.

The point is, people don't obsess about =, they learn the behavior, accept
it, and move on.

- Jon