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))
 
Search JabSto ::




Custom Search