Search code examples
google-sheetsgoogle-sheets-formula

Get the last non empty column value of every row in the spreadsheet

I want to extract the last non empty value in the column of every row in a spreadsheet I prefer to use arrayformula if thats possible

=transpose(ARRAYFORMULA(COLUMN(B2:ZZZ)))

Here is the spreadheet, that I have tried https://docs.google.com/spreadsheets/d/14iTSYnyKycccVvULCSCyG6cRFNdRoY82YzWSyises2c/copy

image.png

Solution

  • In A2 try

    =ArrayFormula(iferror(regexextract(trim(transpose(query(transpose(B2:1000),,rows(B2:1000)))), "[^\s]+$")))

    enter image description here

    In case there are spaces in the data, try

    =ArrayFormula(iferror(substitute(regexextract(trim(transpose(query(transpose(substitute(B2:1000, " ", "~")),,rows(B2:1000)))) , "[^\s]+$"), "~", " ")))

    enter image description here

    and see if that helps?