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)