Reputation: 13

Google Sheets find highest number from right to left

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?

Upvotes: 1

Answers (4)

Erik Tyler

Reputation: 9355

You haven't responded to my last comment below your original post. So I have to guess at some aspects here.

Suppose that Col A in some sheet is completely empty and that your raw-data numbers begin in B2 and may continue as far as Col Z.

Place the following in A2:

=ArrayFormula(IF(B2:B="",,REGEXEXTRACT(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(B2:Z=0,,B2:Z)),,COLUMNS(B1:Z1)))),"\d+$")))

This will give you the rightmost non-zero result for each valid row.

This is an array formula that will process all rows at once, not a drag-down formula.

If your furthest column in which a raw number may exist is earlier or later than Col Z, adjust all instances of Z in the formula to the actual rightmost column in which raw numbers may appear.

Upvotes: 0

player0

Reputation: 1

use:

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

Upvotes: 1

korzck

Reputation: 318

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)

Upvotes: 1

MattKing

Reputation: 7783

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

Upvotes: 1

Google Sheets find highest number from right to left

Answers (4)

Related Questions