Excel cell format overides my input 1

[engmaster]

I am builing a bill of materials and need to input the number "5-1" (w/o quotes) into a cell. But excel reads it to be "1-May". When I format the cell to be "general" it goes back to the date format anyway.
How can I make the format stay general?

 

[AnotherHiggins]

Excel is trying to be helpful. Isn't that nice?

Formatting the cell to General won't work (as you've noticed). You must first format the cell (or row/column/range) as TEXT, then you can type in 5-1 and not have it change on you.

Or, if this data appears sporadically, you can bypass this behavior by putting a single quote/apostrophe in front of the text as you are entering it.

Type in [COLOR=blue white]'5-1[/color]. You will notice that only [COLOR=blue white]5-1[/color] is displayed: the apostrophe just tells excel that what comes next - no matter what it looks like - is text.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[engmaster]

That did it! A star for you. Muchas Gracias.

 

[AnotherHiggins]

Glad to help.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

FYI,

Excel trying to be helpful does this when you enter a value.

When you enter something like 5-1 or 5/1 or 5/1/2045, it is a TEXT value that Excel assumes that your intent is a DATE.

Then Excel parses the TEXT value in accordance with the regional settings, with respect to the position of month and day. In the instance where the the year part is missing, as in your example, Excel assumes the CURRENT YEAR.

So Excel takes the YEAR, MONTH and DAY that it has parsed from the TEXT entry, and uses those parameters in a function to return a DATE VALUE which is a NUMBER, like TODAY is 39057. A NUMBER like this can be FORMATTED to DISPLAY a DATE or some portion of a DATE.

FYI.

Skip,

_[red][/red]