Last Filled Cell Having Text

[Excel Fox]

For those who have been wondering why the formula =MATCH("ZZZZZZZZZZ",$A:$A,1) doesn't give you the last filled non-numeric cell row when working with non-English language, here's a food for thought.

The above formula as we all know is relying on the fact that there can be no word that is greater (in alphabetic order) than a word that has only Z in it. For an exact match, one would always use the last [match_type] argument as 0. However, by passing 1 [highlighted in red above] as the argument, the formula looks for the closest value that is just lower in the alphabetic order. In that sense, ZZZZZZZZZY is lower than ZZZZZZZZZZ, hence the formula returning the last row number.

What is interesting to note is that for non-English languages, we use alphabets 'greater' than Z.

Examples would be alphabets, or in Excel term, Characters like ặ, Ể, Ỉ, Ồ, Ụ, Ỵ etc etc....

To compensate for this, use a combination of the highest character that there can possibly be; and how do you know which is the highest character? Well, Excel has the good old CHAR function which takes a number argument. And the largest character is the 255th character, so the function CHAR(255) would give you this. How do you ultimately use this in your formula, well, just use a combination of MATCH, CHAR & REPT.

Your formula would look like this =MATCH(REPT(CHAR(255),10),$A:$A,1)

[byundt]

If cell A1 contains the word "dog" and A2 contains the word "zebra" and the rest of the column is empty, you would expect the suggested formula to return 2. To the contrary, it returns 1.

In the Arial font, it appears that CHAR(158) sorts last. This is a lowercase z with a circumflex accent above it. The CHAR(158) is a lower case y with umlaut above it.

I listed the ASCII characters 1 to 255 in column A and their ASCII code in column B. I then sorted columns A and B by column A in ascending alphabetical order. The results of the MATCH formulas were:
=MATCH(REPT(CHAR(255),10),$A:$A,1) returns 251 (the correct position of CHAR(255) in column A after sorting
=MATCH(REPT("y",10),$A:$A,1) also returns 251
=MATCH(REPT(CHAR(158),10),$A:$A,1) returns 255
=MATCH(REPT("z",10),$A:$A,1) also returns 255


I cannot test non-Latin fonts, being unable to read those languages. Do the results differ there?

[Excel Fox]

Interesting point byundt..... but yes, it does differ there.... so it seems a combination of CHAR(255) and CHAR(158) combined with the MAX function could yield a desired result?

[Excel Fox]

byundt match issue.JPG by the way, I don't get that anomaly which you mentioned

[byundt]

I repeated my experiment with both Arial and Calibri fonts in Excel 2010. Got the same results (different from yours) each time.
MATCH + Sort Order.jpg

[excelgeek]

I get the same results as byundt in 2003, 2007 and 2010 on English regional settings.
Char(158) appears a better bet in most regional options (and safer than "z") though I can't say what would happen in a non-English version of Excel.