Results 1 to 6 of 6

Thread: Last Filled Cell Having Text

  1. #1
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10

    Last Filled Cell Having Text

    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)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  2. #2
    Junior Member
    Join Date
    Jul 2011
    Posts
    2
    Rep Power
    0
    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?

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    byundt match issue.JPG by the way, I don't get that anomaly which you mentioned
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Jul 2011
    Posts
    2
    Rep Power
    0
    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

  6. #6
    Junior Member
    Join Date
    Apr 2011
    Posts
    6
    Rep Power
    0
    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.

Similar Threads

  1. Replies: 22
    Last Post: 03-19-2013, 07:57 AM
  2. Replies: 5
    Last Post: 03-09-2013, 09:01 AM
  3. Storing Text Value From Cell To Array
    By marreco in forum Excel Help
    Replies: 2
    Last Post: 02-15-2013, 01:11 AM
  4. Replies: 19
    Last Post: 02-11-2013, 10:49 PM
  5. Get last Filled Cell address in a Range.
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 3
    Last Post: 03-24-2012, 01:08 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •