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))
