Home > Back-end >  Google Sheets find highest number from right to left
Google Sheets find highest number from right to left

Time:09-13

I have data in a spread sheet in rows as below of varying length:

1 0 0 0 1 2 0 0 0 1 0 1 2 3 4 5 6 0

1 0 1 2 0 0 1 0 1 2 3 4 0 0 0 0

I need a formula to return the last number from the right that is above zero.

Can anyone please help?

CodePudding user response:

=INDEX(SPLIT(A2," 0"),COLUMNS(SPLIT(A2," 0")))

CodePudding user response:

This query selects an array where every element is greater than 0 and gets the very right symbol of this array.

=RIGHTB(ARRAYFORMULA(TEXTJOIN("";TRUE;IF(A:A>0;A:A;"")));1)

CodePudding user response:

use:

=INDEX(REGEXEXTRACT(TRIM(REGEXREPLACE(A1:A2, "\b0\b", )), "\d $")*1)

enter image description here

  • Related