Getting the last non-empty cell in a row

16
votes

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!

3
excelexcel-formula
Thanks for the reply. See edited. Please note I am completely new to Excel and never worked with formulas before. Copy/paste is fine. – AnchovyLegend
Don't worry, that's fine :) Could you give some more detail about your situation? What I'm expecting is something along how the data is distributed, are all the cells filled up (contain no blanks) until the last cell in that row (But a picture is worth a thousand words!)? Where is the 'new column' (if different sheets are involved, the sheet names will go in formulas). – Jerry
There is only one sheet. All cells are non-empty until the last column+1. Some rows end with column C, some with column D, some with column E. I just need the last cell in every row copied/pasted to the 'new column' J. – AnchovyLegend
Check this question – sancho.s ReinstateMonicaCellio

3 Answers

23
votes

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

8
votes

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.

1
votes

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)