Position of Last Text Entry in Column - Formula

[SDruley]

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

[vlady]

can you try this

=MATCH(9.99999999999999E+307,A1:A20)

[Moo the Dog]

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

Unfortunately, same result as you described.

- Moo

[Moo the Dog]

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.

[bebo021999]

@Moo: Your last formula include cells with space !
Try this:
=LOOKUP(2,1/(CODE(A:A)<>32),ROW(A:A))

[Moo the Dog]

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

[:) Sixthsense :)]

@bebo021999,

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

[bebo021999]

@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.

[:) Sixthsense :)]

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...

[bebo021999]

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))

[:) Sixthsense :)]

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

[SDruley]

Moo the Dog,

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

[Moo the Dog]

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?