Address of the first empty cell in a row

In some cases it is useful to know the address of the first empty cell in a row. This is a way to do this without macros. Write the following formula and press CTRL+SHIFT+ENTER. Excel adds braces { } and saves it as array formula.

=ADDRESS(row number,COLUMN(INDEX(firstCell:lastCell,1,MATCH(TRUE,LEN(firstCell:lastCell)=0,0))))

Example 1

 {=ADDRESS(3,COLUMN(INDEX(B3:F3,1,MATCH(TRUE,LEN(B3:F3)=0,0))))}

Example 2

When (in which month) the department interrupted filling data? We are searching for the first blank cell in the row of department 1, 2, 3 and 4. Formula for the first department:

{=INDEX($B$1:$M$1,1,MATCH(TRUE,LEN(B2:M2)=0,0))}

Note: Value #N/A means that there is no missing data.

And which cell is it?

{=ADDRESS(ROW(B2),COLUMN(INDEX(B2:M2,1,MATCH(TRUE,LEN(B2:M2)=0,0))),4)}

Add a comment »2 comments to this article

  1. What if you wanted to know the first non-empty cell in the row, only parsing from the right, not the left. How would you do that?

    Reply

  2. This worked perfect… thanks!

    Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress