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:
- Google Sheets formula to find the last non-empty column number in a row.
- Column letter of the last used/non-empty column in a row – Google Sheets Formula.
- 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:
Hooray, I solved my problem. I used the ArrayFormula presented at the very beginning of this article to set up three cells in my worksheet to return column numbers instead of column letters. Then I used curly brackets in my query in order to reference these three cells:
1:
=ArrayFormula(IFNA(match(2,1/($A2:2"")))-2)2:
=ArrayFormula(IFNA(match(2,1/($A2:2"")))-1)3:
=ArrayFormula(IFNA(match(2,1/($A2:2""))))Formula:
=QUERY({'Multi-Course'!$A1:BJ}, "SELECT Col5 WHERE Col"&'Multi-Course'!$I1&"='Q' and Col"&'Multi-Course'!$J1&"='Q' and Col"&'Multi-Course'!$K1&"='Q' LABEL Col5 'Player'",1)Yours is the first place I look for Google Sheets solutions. Thank you for putting out this most helpful series of articles.
Hi, Roger Ederle,
I have read this comment after posting a reply to your earlier thread.
I hope the below tutorial will help other readers to learn.
Get the Last Column from a Data Range in Google Sheets.
How can I use this in a Query?
=QUERY($A4:BJ, "SELECT E WHERE T='Q' and U='Q' and V='Q' LABEL E 'Player'",0)Where T, U, and V are the last three columns that have data (the letter ‘Q’) with many more empty columns to the right of the last data column.
I add more data to the range A4:BJ every week, one column per week ending with column BJ in the last week of the year.
I always want to select E for the last three data columns. I would like to automate this.
Right now, I manually change the letters of the last three columns every week. With many queries for different data items, this becomes very tedious.
Thanks.
Hi, Roger Ederle,
You can try this.
=ArrayFormula(QUERY({Sheet1!A4:BR}, "SELECT Col5 WHERE "&"Col"&IFNA(match(2,1/(Sheet1!A5:5<>"")))-2&"='Q' and Col"&IFNA(match(2,1/(Sheet1!A5:5<>"")))-1&"='Q' and Col"&IFNA(match(2,1/(Sheet1!A5:5<>"")))&"='Q' label Col5'Player'",1))Assume your data is in Sheet1. Insert this formula in Sheet2.
I have used the same method to find the last non-empty column and implemented the same in the Query function WHERE clause.
For the last 3 non-empty columns, I have used the below logic.
Last_non_empty_column-2, Last_non_empty_column-1, and Last_non_empty_column.