Formula to return address of first blank cell in row?

[BonnieM]

I have a large spreadsheet (57,000 rows of mostly text cells) and need to identify the first blank cell (or if easier, the last populated cell,) in each row, between columns A and X, and put that cell address in column AK.

I will then use formulas to retrieve certain data to the left of that blank cell and populate that data into addt'l columns. I would prefer a formula if possible to find the blank cell, rather than writing a macro.


I've tried several options without success - all return #N/A:

=CELL("Address",INDEX(A6:X6,MATCH(2,1/ISBLANK(A6:X6))))

=CELL("address",INDEX(A5:X5,MATCH(2,1/(A5:X5=""))))

=INDEX($1:$1,MAX(IF($A4:$X4<>"",COLUMN($A4:$X4))))

(And yes, I used Crtl-Shift-Enter)

<colgroup><col></colgroup><tbody>
</tbody>

There's gotta be a way . . .

 

[Joe4]

Take a look at post #4 here: Formula to find first empty cell in row and next empty cell in row

If you borrow that functionality, we can convert the column number to a letter using the CHAR function and add in the current row number like this:

=CHAR(MATCH(TRUE,INDEX(A1:X1="",0),0)+64) & ROW()

 

[BonnieM]

Thanks Joe, but I'm a bit of a novice here.

I tried it and it returned #N/A

Can you explain the logic of this formula?

What it the significance of the 64?

Should I put anything between the parens at the end? (I tried entering row # and it gave me a formula error)

Appreciate any help . . .

 

[Joe4]

The formula I gave you is for row 1. If you are placing it in any other row, change the row number reference.
For example, if you were putting it on row 4, change it to:

=CHAR(MATCH(TRUE,INDEX(A4:X4="",0),0)+64) & ROW()

The CHAR function returns characters by their ASCII values (see this for an ASCII table: Ascii Table - ASCII character codes and html, octal, hex and decimal chart conversion)

The ASCII value of "A" is 65, "B" is 66, "C" is 67, etc.
So =CHAR(65) would return A.

So, the formula I referenced in the link only returns the column number. If you want to return the column letter, we need to convert 1 to A, 2 to B, 3 to C, etc.
By adding 64 to what is returned, we can then use the CHAR function, so that 65 returns A, 66 returns B, 67 returns C, etc.

When you use the ROW() function with no parameters, it simply returns the row number the formula is placed in. So, enter =ROW() into any cell and see what it returns. Copy it down other rows and watch its behavior.

Also be sure to check out Excel's built-in Help files for functions like CHAR, ROW, etc. Excel's help files are pretty robust with detailed explanations and examples for the different functions.

 

[BonnieM]

Thanks, I tried this for several rows and it still returns #N/A.

 

[Joe4]

You do not need to use CTRL-SHIFT-ENTER for the formula I posted.

Are your blank cells really blank (or is there a space or formula in them)?
A good test is this formula:
=COUNTBLANK(A1:X1)
If that returns zero, it is not finding any blank cells in that row, and that formula won't work (as there are no blanks).

Can you post a small data sample of what your data looks like? There are tools for doing that mentioned here: http://www.mrexcel.com/forum/about-board/508133-attachments.html