Return the Last TEXT Value in a Column

On a number of forums, I found the following suggested formulas for returning the last TEXT value in a column. These are pretty good and they allow the range to include blanks, error values (like #N/A), and numeric values.

=VLOOKUP(REPT("z",255),A:A,1)
  or
=INDEX(A:A,MATCH(REPT("z",255),A:A))

(For the formula that returns the last NUMERIC value in a column, see my previous post)

I prefer the formula that uses MATCH. The reason for the 255 repeated z's for the lookup value is to ensure that the lookup value is larger than any other text value in the range. "Larger" means that if you were to sort the values in ascending order, the largest value would be listed last. In the above formulas, if there are no text values within the lookup range greater than or equal to the lookup value, the MATCH function will return the position of the LAST text value.

The problem with these formulas is that there ARE characters that are larger than "z". If you look up "Default Sort Order" in the Excel help, they don't mention all special characters. Here is an excerpt from the Excel 2003 help documentation:

Text and text that includes numbers are sorted 
in the following order:
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , 
. / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D 
E F G H I J K L M N O P Q R S T U V W X Y Z

It turns out that all the Greek characters (alpha through Ω) come after "z" in the sort order. I mean the Greek characters in the Unicode data set, not characters just displayed using the Greek font.

I didn't test ALL the Unicode characters, but I did find that Cyrillic, Hebrew, and some Arabic characters also come after "z" in the sort order. Most of the special characters like ¢, £, ©, ®, °, ¶, •, …, €, and ™ come BEFORE "z" in the sort order, so if you never use any of the Greek, Cyrillic, Hebrew, or Arabic characters in your spreadsheet, using REPT("z",255) for the lookup value may be just fine.

If you like to use Greek letters (like I do), using the Capital Letter Omega in the lookup value may be appropriate:

=INDEX(A:A,MATCH(REPT("Ω",255),A:A))

To enter the special character Ω in Excel you can use your Character Map (Start > Accessories > System Tools), or you can use the shortcut keyboard sequence Alt+234 (Press and hold the Alt key, then use the numeric keypad to enter 234 and then let go of Alt).

References:
- ANSI Character Set
- Collation
- Enter Unicode Characters in Windows
- Greek Capital Omega
- Entering Special Characters in Excel