A Little Thing About OpenOffice Number Formats in Writer Tables and Calc Spreadsheets

I was just putting together a proposal, putting in the numbers for each category in a Writer table, just like I always do. I was applying automatic number formatting for the currency amounts.

The number formatting was being really annoying, though. I set the number format for a bunch of blank cells, then had to set it again when I put in the numbers since it didn't take.

I also had to re-apply currency formatting when I changed a number. It was $800.00, formatted correctly, and when I changed it to 900, it left-aligned itself and changed its number formatting to Text. That was particularly annoying.

Before we continue,  why do you care about number formatting? It's convenient but there's more. Note that the particular issue I encountered exists in Writer, but the problems if you don't have correct number formatting exist in both  Writer and Calc.

• It's easier to apply formatting that will make decimals and currency symbols appear than to retype it.
• Sorting won't work correctly if the numbers don't know they're numbers.
• Calculations won't work correctly if the numbers don't know they're numbers.

Back to the topic at hand -- why was this happening? Why was number formatting misbehaving? It usually doesn't. But it was today. It's because the default setting "Number Recognition" had been removed. Or maybe it's not on by default in 2.1.

Making Sure Your Number Formatting Sticks
Here's what to do to prevent number formatting annoyance.

1. Create your table.

2. Select the cells that you want to have numbers in.

3. Right-click and choose Number Recognition. You want a checkmark to appear next to  This will keep OpenOffice.org aware that the content you enter is, indeed, numbers and that the program should darned well apply number formatting when you tell it to.

4. Now, apply the appropriate number formatting. Right-click again on the selected cells and choose Number Format. Have your way with it, select the options you want, then click OK.

Notes on How to Detect Incorrectly Formatted Cells
A correctly formatted cell is right aligned like these.

You can have the currency symbol and decimals applied correctly, BUT if the numbers are left aligned, then they still don't know that they're numbers. They think they're text. Weird, huh? So keep an eye out for left-aligned correctly formatted numbers.

---

Comments

[Josh]

Thanks for the tip, do you know how to change the default styles for the number recognition? i.e. instead of 01/01/08, I want to always have it display 01/01/2008 and I can't quite seem to find where to set the defaults

[Solveig]

I don't *think* that can be done. Defaults liked to the icons are buried in the code. You could create a style and link it to a keyboard shortcut, though.

http://openoffice.blogs.com/openoffice/2007/04/creating_keyboa.html