Find first non-empty cell in a spreadsheet row

Question

I've got a fairly simple Excel spreadsheet with many rows of data. Each row contains empty and non-empty cells. Is there a simple way to determine the column letter/number of the cell in each row that contains the first non-empty cell. Ideally I'm after an Excel function to do the job, but I can't find one in the help.

  A|B|C|D|...
1|3|2|0| |...
2| | |1|2|...
3| |2|7| |...

If the above is my spreadsheet, then the solution for rows 1,2,3 would be A,C,B (or 1,3,2).

Answer 1

Try the MATCH function.

Place this in Cell E1: =MATCH("",A1:D1,0)

That will give you the column number of the first empty cell.

To convert to the column letter, use: =CHAR(64+MATCH("",A1:D1,0))

---

One other thing, MATCH will only work if the blank cells contain this formula: =""

If they are truly blank, the formula will return #N/A

So, if you have true blanks, I would suggest making a second table that is almost a copy of the first table. Follow these steps for your example:

1. Select cell F1
2. Enter formula =if(A3="","",A3)
3. Copy cell F1
4. Select F1:I4
5. Paste

Now, redo your MATCH formula in position J1, and change your ranges to the copied table.

A pain, but should work.

---

Easier idea: If it won't mess anything up, just select your table and do a find/replace. The "find what" box should be completely blank. The "replace with" box should be: =""

Answer 2

This will work, but it's an array formula, just substitute your range for A1:D1:

=MATCH(TRUE,LEN(A1:D1)<>0,0)

If you want to check a whole row use, just subtititute your row number for both 1s:

=MATCH(TRUE,LEN(1:1)<>0,0)

Note: Match returns relative values, so if your accessing less than a row, make sure and start with an 'A' column reference, or offset the result accordingly.

Answer 3

The answers above lead me to the following answer at Mr Excel

=MATCH(TRUE,INDEX((A1:D1<>0),0),0)

The full link to that forum is: http://www.mrexcel.com/forum/showthread.php?t=349154

It works in my testing, but I'm still not quite sure how it works.