Excel spreadsheet functions - Funzioni foglio di calcolo

When working with Microsoft Excel, you will find the need to use spreadsheet functions sooner or later. Those functions can be put directly into a cell and do not require any knowledge of Basic.

However, their names are translated into the application language so non English copies will have localized function names; this means you will need to translate them into your locale before being able to use them otherwise you will get the error #NAME?

Remember that this operation is not necessary in order to view Excel files as upon opening they will automatically be converted to work with the application locale.

Here is a descriptive list of Excel 2003 functions.

Here you can translate function names between different languages.

Note: the same applies to OpenOffice.org / LibreOffice Calc

[SQL] Oracle LIMIT clause

To limit the number of rows returned by a query, in standard SQL you would:

SELECT *

FROM table

WHERE [conditions]

LIMIT number

In Oracle you just add ROWNUM to the WHERE clause like:

SELECT *

FROM table

WHERE [conditions] AND ROWNUM=1

To have the equivalent of SQL "LIMIT 1"

[PL/SQL] Test a string for a numeric value

In PL/SQL, you can easily check whether a string contains numeric values with:

LENGTH(TRIM(TRANSLATE(string, '0123456789', ' ')))

This will return null if the string contains only numeric characters otherwise it will return the number of non-numeric characters in it.