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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Doctor Logic

New Member
Joined
Oct 24, 2002
Messages
40
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.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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.
 

Doctor Logic

New Member
Joined
Oct 24, 2002
Messages
40
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!)
 

cgreen

Active Member
Joined
Aug 14, 2002
Messages
290

ADVERTISEMENT

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
 

attc

Board Regular
Joined
Oct 11, 2002
Messages
87
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
 

Doctor Logic

New Member
Joined
Oct 24, 2002
Messages
40
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.
 

cgreen

Active Member
Joined
Aug 14, 2002
Messages
290
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,817
Messages
5,598,275
Members
414,221
Latest member
MD1222

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
Top