Excel find first word without IFERROR() or IF(ISERROR())

In Excel, to find the first word in a cell, find the position of first space then return all characters up to but excluding the space: =LEFT(A1,FIND(" ",A1)-1).

The formula above fails with #VALUE if the string in A1 doesn't have a space or the cell is empty. A Excel 2007 solution is to wrap the entire formula in IFERROR to return a default value: =IFERROR(LEFT(A1,FIND(" ",A1)-1),A1). Excel 2003 doesn't have IFERROR, so the formula is much longer with the same formula for the condition and output of the IF formula: =IF(ISERROR(LEFT(A1,(FIND(" ",A1)))),A1,LEFT(A1,(FIND(" ",A1)-1))).

A shorter solution for Excel 2003 onwards is to append a space to the cell value in the FIND formula so that a default value can always be returned: =LEFT(A1,FIND(" ",A1&" ")-1). Note: Test if it works for OpenOffice Calc.

Australian Accounting Number Formats

Number format codes for Excel and Libre Calc used in Australian accounting (Excel does not have this number format code available as a default while Libre Calc has the second one).

Number format code	123456.78	-123456.78	0.00
#,##0_);(#,##0);-_)	123,457	(123,457)	-
#,##0.00_);(#,##0.00);-_)	123,456.78	(123,456.78)	-

Notes:

• Semicolons delimit the sign of the number being formatted: positive;negative;zero.
• The underscore before the right parenthesis: append a trailing space the size of the parenthesis so that positive numbers are right-aligned with negative numbers (which are enclosed in parentheses).
• Using the first format code, your numbers are formatted without cents.

The underlying value in the cell hasn't changed, just the presentation. Use sparingly; you can confuse a user who tries to use what they see in the cell in their formula and wonder why their formula doesn't work.

Automatically Update Last Row in Range Formulas

In OpenOffice Calc, worksheet formulas that apply a range of rows, such as SUM(), need to be updated when an extra row is added just above the cell with that formula. For example, if you start the following worksheet values and formulas ...

  A
1 4
2 7
3 9
4 =SUM(A1:A3)

... then when you insert a new row between rows 3 and 4, OO-Calc does not automatically update the second argument to the SUM() formula (Note: MS-Excel 2003 does). A solution is to use the OFFSET formula to automatically update the second argument like this: =SUM(A1:OFFSET(A4;-1;0;1;1)). In this example, the OFFSET returns a reference to a cell one row above cell A4.

See Also

• OFFSET Function

OpenOffice Calc AutoInput capitalization annoyance

When you enter text in a cell in OpenOffice Calc, you might find that your text is incorrectly capitalized and you cannot correct it! For example, while writing a vocabulary list, I entered 'sun' and it was capitalized as 'Sun' (maybe referring to a certain technology company). If you type Ctrl+Z to undo this capitalization, all the text in the cell is removed, not just the capitalization. It seems that Calc's AutoInput feature is automatically capitalizing text before it is entered (unlike 'AutoCorrect', which makes the correction after the text is entered, so you can undo the capitalization). I don't know how AutoInput differs from AutoCorrect, and I haven't found AutoInput useful so I've disabled it using menu item Tools / Cell Contents / AutoInput.