Excel Fox
06-28-2011, 04:25 PM
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)
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)