Microsoft Office Tutorials and References

In Depth Information

**Advanced Text Formulas**

This is a rather complicated formula, so it may help to examine its components. Basically, the

formula works as follows:

1.
If the last two digits of the number are 11, 12, or 13, use
th.

2.
If Rule #1 does not apply, check the last digit. If the last digit is 1, use
st.
If the last digit is

2, use
nd.
If the last digit is 3, use
rd.

3.
If neither Rule #1 nor Rule #2 apply, use
th.

The formula uses two arrays, specified by brackets. Refer to Chapter 14 for more

information about using arrays in formulas.

Figure 5-5 shows the formula in use.

Figure 5-5:
Using a formula to express a number as an ordinal.

Determining a column letter for a column number

This next formula returns a worksheet column letter (ranging from A to XFD) for the value

contained in cell A1. For example, if A1 contains
29,
the formula returns
AC.

=LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1)

Note that the formula doesn’t check for a valid column number. In other words, if A1 contains a

value less than 1 or greater than 16,384, the formula then returns an error. The following

modification uses the IFERROR function to display text
(Invalid Column)
instead of an error value:

=IFERROR(LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1),”Invalid Column”)

The IFERROR function was introduced in Excel 2007. For compatibility with versions prior to

Excel 2007, use this formula:

=IF(ISERR(LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1)),

“Invalid Column”,LEFT(ADDRESS(1,A1,4),FIND(1,ADDRESS(1,A1,4))-1))