Are you looking for a way to find the last non-empty column in a row in Google Sheets?

Here I have solutions for the following using Match as the main formula in Google Spreadsheets.

Solutions based on the last non-blank/non-empty cell in a row:

1. Google Sheets formula to find the last non-empty column number in a row.
2. Column letter of the last used/non-empty column in a row – Google Sheets Formula.
3. Google Sheets formula to find the last non-empty cell ID in a row.

The formula that you will get on this tutorial won’t work in Excel as it involves ArrayFormula and Regexreplace functions.

That doesn’t mean Excel has no solutions to find the last used cell address, column number or column letter in a row. You can re-write the formulas that I am going to share with you below with other similar functions.

I mean replace Regexreplace with Substitute and ArrayFormula with the Excel alternative. If you want that formula, let me know using the comments below.

You May Like: Array Formula: How It Differs in Google Sheets and Excel.

I am only detailing the Google Sheets part here. Let’s start with how to find the last non-empty column number in a row in Google Sheets.

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

Using Match we can find the last used/non-empty column in a row.

Assume I want to find the last used column number of row #3. I can use the following formula for that.

Formula 1:

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

The text “Total” is in column 6 in the third row. Even though there is a blank cell between the cells containing the strings “Total” and “Mar”, the formula finds the correct column number 6.

Logic and Formula Explanation

The logic or you can say the key lies in the array generated by 1/(A3:3<>"").

To test, key the formula =ArrayFormula(1/(A3:3<>"")) in cell A4. The output will be as below.

The formula returns the number 1 wherever the cells in row#3 have values, else #DIV/0! error.

Now please refer to the syntax of the Match function (Match returns the relative position of an item in a range).

MATCH(search_key, range, [search_type])

Let me explain the values used in place of each argument.

search_key – 2

range – 1/(A3:3<>"")

search_type – leave the search_type. It’s optional to use and the default is 1 (means sorted range)

Let the formula to take the default search_type that to consider the range is sorted, that in A-Z (ascending order).

So what happens?

The formula would return the largest value less than or equal to the search_key from the A-Z sorted range.

The search_key is the number 2 which is not available in our formula-generated virtual range (please refer to the screenshot above). So the Match will search for the largest number less than 2.

The largest value which is less than 2 is 1. Since there are multiple cells containing the number 1, the formula would return the relative position of the last cell containing the number 1.

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

I have given the formula to find the last non-empty column number in a row above. Also, you have got the formula explanation in detail, right?

From that column number, we can find the column letter of the last non-blank column in a row. How?

In a very recent tutorial, I have provided a formula and formula explanation related to converting column number to letter – How to Convert Column Number to Letter in Google Sheets.

We can use the same formula given in that tutorial here. Here is that Generic formula.

=regexextract(address(1,column_number_to_convert),"[A-Z]+")

In this generic formula, replace 1 with the row number in which you want to find the last used column letter. Here it is row number 3.

Then replace column_number_to_convert with our formula 1 above which is=ArrayFormula(IFNA(match(2,1/(A3:3<>"")))).

The final formula to find the column letter of the last non-empty column in row # 3 will be as follows.

Formula 2:

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

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

If you have gone through the above formulas, then finding the cell address of the last non-empty cell in a row in Google Sheets will be just kids’ play.

Copy Formula 2 above and remove the Regex part and voila!

Formula 3:

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

I hope you could understand how to find the last non-empty/non-blank column number/letter/ID in a row in Google Doc Spreadsheets.

Additional Resources:

1. Find the Cell Address of a Last Used Cell in Google Sheets.
2. How to Find the Last Row in Each Group in Google Sheets.
3. How to Find the Last Matching Value in Google Sheets.
4. Lookup Last Partial Occurrence in a List in Google Sheets.
5. How to Find the Last Value in Each Row in Google Sheets.