Please stop the date format from taking over my spreadsheet!

Doctor Logic

New Member
Joined
Oct 24, 2002
Messages
40
Please stop the date format from taking over my spreadsheet!

This is in my cell: -1/0 and when I try to remove the - it turns into a date. The correct entry is 1/0 meaning One Oaugt Wire Size.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Yes, I did do that but it did not solve the problem. I have quite a long list of these things so I sure do not want to type them all in. Wire sizes are stuff like: 1/0 2/0 3/0 and so forth.
 
Upvote 0
Strange. If I type 1/0 in a cell I get just that. If I type -1/0 I get an error, because Excel thinks I am using R1C1 notation.

That's in Excel 97 and 2000.
 
Upvote 0
Well, the original text in the cell was somthing like this:

WIRE THW-1/0-BLK-19STR-CU-1000R

And I used find and replace to eliminate all the stuff until I got this:

-1/0

Now I can't get the - out of there for the life of me. Everything I try returns:

Jan-00

(Arrgh!)
 
Upvote 0
The statement below was in the help menu for Excel. Try clearing your formatting in the cells or columns you are changing, then format as text.

Clear contents, formats, or comments from cells

1 Select the cells, rows, or columns you want to clear.
2 On the Edit menu, point to Clear, and then click All, Contents, Formats, or Comments.

Notes

· If you click a cell and then press DELETE or BACKSPACE, Microsoft Excel removes the cell contents but does not remove any comments or cell formats.
· If you clear a cell, Microsoft Excel removes the contents, formats, comments, or all three from a cell. The value of a cleared cell is 0 (zero), and a formula that refers to that cell will receive a value of 0.

Tip To remove all comments from a worksheet, click Go To on the Edit menu, click Special, and then click Comments. Then point to Clear on the Edit menu, and click Comments.

good luck
 
Upvote 0
suggestions:
(i) you must format as TEXT *before* removing the '-'

[edit] sorry-- this doesn't work when you use edit->replace I just realised. SO...

(ii) replace the - with ' is the best solution.

AP - what did you mean? I get #DIV/0! when I enter -1/0 in a cell... as expected?!
This message was edited by attc on 2002-11-07 13:39
 
Upvote 0
Yes, Yes, I do know about clearing the formats. The problem is that when I do the find and replace excel thinks the result (1/0) is a date and automatiacally formats the cells to date format even though I formatted them to TEXT previously. I'm not doing only one cell but thousands at the same time so the F2 / ENTER opioon is out of the question.

It's a real nasty one. My editor friend on the other side of the wall has no answer to the problem either.
 
Upvote 0
On 2002-11-07 13:31, attc wrote:
suggestions:
(i) you must format as TEXT *before* removing the '-'

[edit] sorry-- this doesn't work when you use edit->replace I just realised. SO...

(ii) replace the - with ' is the best solution.

AP - what did you mean? I get #DIV/0! when I enter -1/0 in a cell... as expected?!


I get the same as you (DIV/O). It automatically turns it into a formula, but when I manually remove the '=' then it leaves me with the result of '-1/0'. After doing some research, if you are planning on adjusting a large amount of cells then your best bet is to not use " - ", but " ' " as suggested.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top