+ Reply to Thread
Results 1 to 13 of 13

Position of Last Text Entry in Column - Formula

  1. #1
    Valued Forum Contributor SDruley's Avatar
    Join Date
    04-27-2009
    Location
    Conover, NC
    MS-Off Ver
    Excel 2010 64 bit
    Posts
    415

    Position of Last Text Entry in Column - Formula

    I have a column of text values listed below. I need the position of the last text value which is 7, however, the situation is complicated by the fact that the cells that appear blank contain the null string [""]. So, I keep getting the answer 9 when trying to use some of the conventional formulas. From cells 10 and down the cells are truely blank.


    A
    1 Orange
    2 Orange
    3
    4 Bannana
    5
    6 Apple
    7 Orange
    8
    9
    Turn Data into Information
    Turn Information into Knowledge
    Turn Knowledge into Direction
    Turn Direction into Leadership
    Turn Leadership into Results
    Stephen Druley

    It's not how quickly you think
    But how deeply you think
    The quality of thinking is measured
    by remoteness to conformance
    Stephen Druley

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Position of Last Text Entry in Column - Formula

    can you try this

    =MATCH(9.99999999999999E+307,A1:A20)
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Position of Last Text Entry in Column - Formula

    I tried using: =MATCH(REPT("z",255),(A:A))

    Unfortunately, same result as you described.

    - Moo

  4. #4
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Position of Last Text Entry in Column - Formula

    How about this as an array formula (entered with Ctrl + Shift + Enter):

    =MATCH(REPT("z",255),IF(A:A<>"","z"))

    - Moo

    * Note: To reduce the effect of the array formula, you may wish to limit the height of the array/range. Instead of using the entire column (A:A), just use a range that contains more rows than you will likely need.
    Last edited by Moo the Dog; 12-21-2012 at 02:17 AM. Reason: Added note

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,675

    Re: Position of Last Text Entry in Column - Formula

    @Moo: Your last formula include cells with space !
    Try this:
    =LOOKUP(2,1/(CODE(A:A)<>32),ROW(A:A))
    Last edited by bebo021999; 12-21-2012 at 02:13 AM.
    Quang PT

  6. #6
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Position of Last Text Entry in Column - Formula

    Good point, bebo. I wasn't testing for a space.. the OP was concerned with a null string. Ya never know when one of those evil spaces will attack.

    - Moo

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Position of Last Text Entry in Column - Formula

    @bebo021999,

    Just for your quick notice what happens if there is a Number after the Text data?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,675

    Re: Position of Last Text Entry in Column - Formula

    Quote Originally Posted by :) Sixthsense :) View Post
    @bebo021999,
    Just for your quick notice what happens if there is a Number after the Text data?
    What do you mean"Number after the Text data"?
    i.e: last cell with data in column A is: 123 or abc123?
    anyway, CODE(123) or CODE(abc123) is <>32 is included.

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Position of Last Text Entry in Column - Formula

    Quote Originally Posted by bebo021999 View Post
    What do you mean"Number after the Text data"?
    But I believe OP Named The Title as Position of Last Text Entry in Column - Formula

    So Number should be ignored, I think so...

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,675

    Re: Position of Last Text Entry in Column - Formula

    Quote Originally Posted by :) Sixthsense :) View Post
    But I believe OP Named The Title as Position of Last Text Entry in Column - Formula

    So Number should be ignored, I think so...
    I think 1 more criteria:l

    =LOOKUP(2,1/(CODE(A:A)<>32)*(ISTEXT(A:A)),ROW(A:A))

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Position of Last Text Entry in Column - Formula

    Quote Originally Posted by bebo021999 View Post
    I think 1 more criteria:
    Still it's getting the Number cell as last row

    In fact myself is also curiously waiting for the Non Array Solution for this case

  12. #12
    Valued Forum Contributor SDruley's Avatar
    Join Date
    04-27-2009
    Location
    Conover, NC
    MS-Off Ver
    Excel 2010 64 bit
    Posts
    415

    Re: Position of Last Text Entry in Column - Formula

    Moo the Dog,

    Thanks so much. No spaces are used in my example so your example worked very well. Great work!

  13. #13
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Position of Last Text Entry in Column - Formula

    Thank you, and you're welcome. Feel free to leave appropriate feedback, it's always appreciated.

    - Moo

    ---------------
    I'd like to thank the Academy, and, more importantly, my mom. Without her I wouldn't be here. I'd also like to thank my 3rd grade teacher, Ms. McGovern, without whom I would have never known to respond, "How high?" after being given the command to "Jump!". Apparently, "I'd rather not, I just ate" is NOT the correct response. Who knew?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1