Find the Last Non-Empty Column in a Row in Google Sheets

GiGiKing·March 16, 2024·0 comments

Are you struggling to find the last non-empty column in a row in Google Sheets? Look no further! I have some amazing solutions for you using the Match formula in Google Spreadsheets. Let’s dive in and explore how you can easily find the last non-empty column in a row using these formulas.

Formula to Find the Last Non-Empty Column Number in a Row in Google Sheets

Using the Match function, we can easily find the last used or non-empty column in a row. Let’s say you want to find the last used column number in row #3. Here’s the formula you can use:

Formula 1:
=ArrayFormula(IFNA(match(2,1/(A3:3<>"")))

By applying this formula, you’ll get the last non-empty column number. Even if there are blank cells between the non-empty cells, this formula will accurately identify the correct column number.

Finding Last Non-Empty Column Number in a Row

The output in this example shows that the text “Total” is in column 6 in the third row. Despite the presence of a blank cell between the cells containing the strings “Total” and “Mar”, the formula correctly identifies the column number as 6.

Logic and Formula Explanation

The key lies in the array generated by the expression 1/(A3:3<>""). When you test this formula in cell A4 using =ArrayFormula(1/(A3:3<>"")), you’ll see that it returns the number 1 wherever the cells in row #3 have values and a “#DIV/0!” error for empty cells.

Now, let’s refer to the syntax of the Match function:

MATCH(search_key, range, [search_type])

In our case, the values used are as follows:

search_key: 2
range: 1/(A3:3<>””)
search_type: We leave this blank, as it’s optional and the default value is 1 (for a sorted range)

The formula will return the largest value less than or equal to the search_key from the A-Z sorted range. Since the search_key is 2 and not available in our formula-generated virtual range, the Match function will search for the largest number less than 2.

As a result, the formula will return the relative position of the last cell containing the number 1, which corresponds to the last non-empty column in the row.

Find Column Letter of Last Used Column in a Row in Google Sheets

Now that you have the last non-empty column number, you might also want to find the column letter of that column. No worries, here’s what you can do!

You can use a generic formula that converts column number to letter, as explained in my tutorial on how to convert column number to letter in Google Sheets. Simply replace the row number and the column_number_to_convert in the formula with the formula from “Formula 1” above.

Here’s the final formula to find the column letter of the last non-empty column in row #3:

Formula 2:
=ArrayFormula(regexextract(address(3,IFNA(match(2,1/(A3:3<>"")))),"[A-Z]+"))

By applying this formula, you’ll get the column letter of the last non-empty column in row #3.

How to Find the Last Non-Empty Cell Address in a Row in Google Sheets

If you’ve understood the above formulas, finding the cell address of the last non-empty cell in a row in Google Sheets will be a piece of cake!

Simply copy “Formula 2” above and remove the Regex part. Here’s what the updated formula looks like:

Formula 3:
=ArrayFormula(address(3,IFNA(match(2,1/(A3:3<>"")))))

By using this formula, you’ll get the address of the last non-empty cell in row #3.

I hope these formulas have demystified the process of finding the last non-empty column number, column letter, and cell address in a row in Google Sheets. If you need additional resources or have any questions, feel free to reach out to me through the comments below.

Additional Resources: