last row with data (formula)

[MrDoc]

Hi,

After searching Excel Questions, I found the formula
"=match(9.99999999999999E+307;A:A)" which gives me the number of the last row in column A that contains a number.
It doesn't work, however, if the last cell (or all rows in column A, for that matter) contain other kind of data, like text.
How can I find the last row in column A containing text, without using VBA?
Than you for any help.
Best Regards,
MrDoc

 

[Aladin Akyurek]

Hi,

After searching Excel Questions, I found the formula
"=match(9.99999999999999E+307;A:A)" which gives me the number of the last row in column A that contains a number.
It doesn't work, however, if the last cell (or all rows in column A, for that matter) contain other kind of data, like text.
How can I find the last row in column A containing text, without using VBA?
Than you for any help.
Best Regards,
MrDoc

What you tried is meant for a range of numeric type.

For a range of type text, use:

=MATCH(REPT("z",255),A:A)

in order to get the position of the last text value.

For the last text value itself, use:

=LOOKUP(REPT("z",255),A:A)

 

[Brian from Maui]

Hi,

After searching Excel Questions, I found the formula
"=match(9.99999999999999E+307;A:A)" which gives me the number of the last row in column A that contains a number.
It doesn't work, however, if the last cell (or all rows in column A, for that matter) contain other kind of data, like text.
How can I find the last row in column A containing text, without using VBA?
Than you for any help.
Best Regards,
MrDoc

=INDEX(G:G,MATCH(REPT("z",255),G:G))

=LOOKUP(REPT("z",255),Sheet1!G:G)

 

[MrDoc]

Thanks a lot, Aladin and Brian!
Best Regards,
MrDoc

 

[MrDoc]

Sorry, I forgot to ask how to find the last column with text as well...
Thanks for helping,
MrDoc

 

[Aladin Akyurek]

Sorry, I forgot to ask how to find the last column with text as well...
Thanks for helping,
MrDoc

Do you mean...

=MATCH(REPT("z",255),1:1)

=LOOKUP(REPT("z",255),1:1)

1:1 stands for row 1.

 

[MrDoc]

That's it, thank you, Aladin. I also found out that I can use TRIM() to get the exact text, without trailing spaces, like in
"=TRIM(MATCH(REPT("z";255);1:1))"
Best Regards,
MrDoc

 

[Aladin Akyurek]

That's it, thank you, Aladin. I also found out that I can use TRIM() to get the exact text, without trailing spaces, like in
"=TRIM(MATCH(REPT("z";255);1:1))"
Best Regards,
MrDoc

You mean of course:

=TRIM(LOOKUP(REPT("z";255);1:1))

Right?

 

[MrDoc]

Right
Sorry, my mistake
MrDoc