Here I am going to show you how we can get the last non blank column number, value, and cell address. Now lets consider the active row as "row:1". So if we want to find out the last non blank column in row 1 we can use any of the below formulas.
if range has blank cells in between:
For both text and number
Formula 1: =MAX(IF(ISBLANK(1:1),0,COLUMN(1:1)))
Formula 2: =MAX((1:1<>"")*(COLUMN(1:1)))
Formula 3: =SUMPRODUCT(MAX(($1:$1<>"")*(COLUMN(1:1))))
Formula 4: =LOOKUP(2,1/(1:1<>""),COLUMN(1:1))
Formula 5: =MATCH("",1:1,-1) or MATCH("*",1:1,-1) - For text only
Formula 6: =MATCH(0,1:1,-1) - For numbers only
if range is continuous without blanks:
Formula 7: =COUNTA(1:1)
To get last column value prefix "INDEX" formula with any formula mentioned above as shown in green font:
=INDEX(1:1,MAX((1:1<>"")*(COLUMN(1:1))))
To get last column cell address prefix "ADDRESS" formula with any formula mentioned above as shown in green font:
=ADDRESS(1,MAX((1:1<>"")*(COLUMN(1:1))))
The "1" mentioned at the "beginning" is the row number. Because our data is in '1' row we have kept it as 1.
Thank You!