Excel is interpreting my TEXT... I wish it would not!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Try this - it look a lot like a bug to me:
set a cell to the format TEXT and fill it with '1-2' (in words, one minus
two).
If this had been a STANDARD cell format, Excel would interpret this value as
a date and modify it accordingly when you leave the cell, but quite corectly,
not if you specify the cell to be a TEXT cell... so far so good.
Now use the search and replace tool to surround the minus with spaces. We
would expect nothing more than the value '1 - 2'... unfortunatly what we get
is a date! Somehow Excel has ignored or overwritten the cell format.
I think this is wrong: a cell defined as TEXT should stay TEXT unless I
choose it to be otherwise. Yes, I could add a leading apostrophy, but the
search and replace tool is too weak to prepend all my texts for me!
 
Try this - it look a lot like a bug to me:
set a cell to the format TEXT and fill it with '1-2' (in words, one minus
two).
If this had been a STANDARD cell format, Excel would interpret this value as
a date and modify it accordingly when you leave the cell, but quite corectly,
not if you specify the cell to be a TEXT cell... so far so good.
Now use the search and replace tool to surround the minus with spaces. We
would expect nothing more than the value '1 - 2'... unfortunatly what we get
is a date! Somehow Excel has ignored or overwritten the cell format.
I think this is wrong: a cell defined as TEXT should stay TEXT unless I
choose it to be otherwise. Yes, I could add a leading apostrophy, but the
search and replace tool is too weak to prepend all my texts for me!
Click to expand...


I can confirm this happens in Excel 2002.
--ron
 
Ron Rosenfeld said:
I can confirm this happens in Excel 2002.
--ron
Click to expand...

Same behavior in Excel 2003. I never noticed this before, and I can't
figure out a workaround!
James
 
Puddles said:
Try this - it look a lot like a bug to me:
set a cell to the format TEXT and fill it with '1-2' (in words, one minus
two).
If this had been a STANDARD cell format, Excel would interpret this value
as
a date and modify it accordingly when you leave the cell, but quite
corectly,
not if you specify the cell to be a TEXT cell... so far so good.
Now use the search and replace tool to surround the minus with spaces. We
would expect nothing more than the value '1 - 2'... unfortunatly what we
get
is a date! Somehow Excel has ignored or overwritten the cell format.
I think this is wrong: a cell defined as TEXT should stay TEXT unless I
choose it to be otherwise. Yes, I could add a leading apostrophy, but the
search and replace tool is too weak to prepend all my texts for me!
Click to expand...

The best I can suggest is to add leading apostrophes with a formula. For
text in column A, the formula (for, say, B1) would be
="'"&A1
(that is, an apostrophe between two double quotes followed by &A1).
This can then be copied down the rest of column B.
Then select column B, copy it and use
Edit > Paste Special > Values
This replaces the formulas with the resulting values. You can then delete
column A and use instead these resulting values.
Now, relacing "-" with " - " will change "1-2" to "1 - 2" as you require,
without converting it to a date.
 
In Excel 2007, the same.


Same behavior in Excel 2003. I never noticed this before, and I can't
figure out a workaround!
James- Hide quoted text -

- Show quoted text -
Click to expand...
 
Judging by the replies to this post, we do indeed have a bug here! How does
one escalate this to the developers at Microsoft?
 
It appears to change the cell format to Custom, (d-mmm) and the value
becomes 1/2/2007 and if you then format back to text you get 39084!

This is in Excel 2003, but I suppose all are alike.
 
Back
Top