Excel display BAB values as typed?

[MonkeySage]

Every time I try to type in BAB values in excel, it automatically "corrects" them into a decimal value or date... I cannot figure out how to get excel to not do this.


(If this is in the wrong place, I apologize. I did not know where else to put it.)

[Knaight]

You can format the cells to display zero decimal places. This pretty much handles everything.

[Brother Oni]

It depends on whether you need to use the values of the BAB or not.

If not, format the cells as text and you should be able to enter the '+' character. An alternate way would be to CONCATENATE the text values or just use &"+"&[BAB value]

I don't think it's possible for Excel to pick out the value from a text entry without use of macros, so for aesthetic looks only, I suggest formatting the cell to the left of the BAB cell to right aligned and having the '+' sign in there.

[Ashtar]

If you put a single quote ' at the beginning of the cell, Excel then treats it as text only. And the ' will be invisible because it's excel's special marking.

[Manga Shoggoth]

Alternatly, select the column (or the individual cells) and press Control-1 (that's one, not i) to bring up the "Format Cells" dialogue. Select Text and press OK.

This does much the same as the single quote Ashtar mentions, but means that you don't have to remember about the quote.

With both solutions you can still treat the cell as a number in formulae (at least, a straight addition works).

The snag is that column (or cells) will now treat the contents as pure text. If you need the data right-aligned, then just use the right-align button under the home tab (or wherever it is in your version of Excel).

[Brother Oni]

With both solutions you can still treat the cell as a number in formulae (at least, a straight addition works).


Huh, this works in Excel 2010, I stand corrected.

I guess I'm too used to Excel being really stupid.

[Manga Shoggoth]

Huh, this works in Excel 2010, I stand corrected.

I guess I'm too used to Excel being really stupid.

It also works in 2007. I'm curious now - can you remember what version it didn't work in?

[Brother Oni]

It also works in 2007. I'm curious now - can you remember what version it didn't work in?

98 I think. I don't think I ever tried it in 2000.

[Manga Shoggoth]

98 I think. I don't think I ever tried it in 2000.

Thanks.

Strange. I'll have to reinstall an old version and have a look some time. When I can tear myself away from here, that is...

[AMX]

Just tried it on Excel 2002 ("Excel XP")
It sort-of works, but any change after calculating the formula causes it to break.

Best option seems to be using a Custom format.
I think

+0
should be good enough (always puts a + in front of the number), but just for the hell of it, I've got it set to

+0 #/#;-0 #/#
right now.