Finding the rightmost occupied cell

  • Thread starter Thread starter Paul Hyett
  • Start date Start date
P

Paul Hyett

If I have a range of cells across a row, where only some of the cells on
the left of the range are occupied, is there a way I could extract the
value in the rightmost of the occupied cells (Excel 2003)?
 
If your data is in A19:N19, for example:
=LOOKUP(2,1/(A19:N19<>""),A19:N19)
Yes, I know it looks odd, but it works.

Bob Umlas
Excel MVP
 
The best formula to use depends on what type of data is in the cells.

If the data is numeric:

=LOOKUP(1E100,A1:J1)

If the data is text:

=LOOKUP(REPT("z",255),A1:J1)

And then there's formulas for *mixed* data and formulas to exclude cells
with text formula blanks and formulas for....

So, it depends!
 
Instead of REPT("z",255) use CHAR(255):
=LOOKUP(Char(255),A1:J1)

T. Valko said:
The best formula to use depends on what type of data is in the cells.

If the data is numeric:

=LOOKUP(1E100,A1:J1)

If the data is text:

=LOOKUP(REPT("z",255),A1:J1)

And then there's formulas for *mixed* data and formulas to exclude cells
with text formula blanks and formulas for....

So, it depends!
Click to expand...
 
=LOOKUP(REPT("z",255),A1:J1)

That's the pedantic technique.

If it was my file and I knew the data and application I'd probably use:

=LOOKUP("zz",A1:J1)

--
Biff
Microsoft Excel MVP


Bob Umlas said:
Instead of REPT("z",255) use CHAR(255):
=LOOKUP(Char(255),A1:J1)
Click to expand...
 
Hi,

Very cute!

How about

=LOOKUP("ÿ",4:4)

The item in the first argument is the CHAR(255)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


Bob Umlas said:
Instead of REPT("z",255) use CHAR(255):
=LOOKUP(Char(255),A1:J1)
Click to expand...
 
The best formula to use depends on what type of data is in the cells.

If the data is numeric:

=LOOKUP(1E100,A1:J1)
Click to expand...

The data is a value generated by a formula in each cell.

Thank you to everyone who replied - I'll try each solution to find which
works best.
 
Excel uses it's internal sort order for text comparisons - not character code -
so CHAR(255) won't work in general if there are any z's in the range.

REPT("z",255) is inefficient and won't work in general if there are any
other character sets or unicode symbols in the range.

If it's standard text try a symbol that sorts after z, eg any greek
character:
=LOOKUP("α",A1:J1). To be robust use: =LOOKUP("々",A1:J1)

Running this script... for i=1 to 65535: cells(i,1)=chrw(i): next i
from the immediate window and sorting shows the order.
Several characters including the one above (12293) appear to be outside
the normal order and seem to work in all cases.




Bob Umlas said:
Instead of REPT("z",255) use CHAR(255):
=LOOKUP(Char(255),A1:J1)
Click to expand...
 
Back
Top