Getting the last non-empty cell in a row

问题:

I am having a lot of difficulty trying to come up with a way to 'parse' and 'order' my excel spreadsheet. What I essentially need to do is get the last non empty cell from every row and cut / paste it a new column.

I was wondering if there is an easy way to do this?

I appreciate any advice. Many thanks in advance!

回答1:

Are your values numeric or text (or possibly both)?

For numbers get last value with this formula in Z2

=LOOKUP(9.99E+307,A2:Y2)

or for text....

=LOOKUP("zzz",A2:Y2)

or for either...

=LOOKUP(2,1/(A2:Y2<>""),A2:Y2)

all the formulas work whether you have blanks in the data or not......

回答2:

Okay, from what you've given if I understood correctly, you can use this formula in cell J1 and drag it down for the other rows below this cell:

=INDEX(A1:I1,1,COUNTA(A1:I1))

This assumes that the 'longest row' goes up to the column I.

回答3:

You can also use OFFSET. You don't need to specify an ending column, you can just reference the entire row.

=OFFSET(1:1,0,COUNTA(1:1)-1,1,1)