Need to Display last 3 numbers from selection of 10 cells - without zeroes

**simmo6477** · 05-24-2013, 07:43 AM

I have 10 columns into which the numbers 1 to 4 (based on grades from elsewhere) are called into. The right hand columns may not yet have data in them and are therefore showing zero. The same number can appear more than once. I would like to show the last 3 (or fewer if less than 3) of these in a single cell in Column 11. For instance 1, 1, 2, 3, 2, 0, 0, 0, 0 ,0 would become 232; 1, 4, 0, 0, 0, 0, 0, 0, 0 ,0 would become 14; 1, 1, 2, 3, 2, 4, 3, 1, 2 ,3 would become 123.

Are you able to help please?

**Lemice** · 05-24-2013, 08:03 AM

Hello and welcome to the forum,

One direct and rough way to do this is by using this Array formula

Please Login or Register  to view this content.

Assuming you have ten random one-digit number from A1 to J1, paste that formula on K2.

Note that because it is an Array formula, you will need to confirm with Ctrl-Shift-Enter, not just Enter. A way to do this is that after you paste the formula on K2, click on the formula bar (to edit it), then hold Ctrl-Shift and hit Enter. If you see the formula being wrapped inside a { } then you did it right.

**simmo6477** · 05-24-2013, 08:52 AM

First class response - resolved the issue with which I have been battling all day!

Many thanks.

**ChemistB** · 05-24-2013, 08:56 AM

Here's another solution, again, this is an array

=INDEX($A1:$J1,MATCH(2, 1/(A1:J1<>0))-2) &INDEX($A1:$J1,MATCH(2, 1/(A1:J1<>0))-1) & INDEX($A1:$J1,MATCH(2, 1/(A1:J1<>0)))

NOTE: Mine messes up if there are less than 3 values, might need an IF in there to catch that.

**Ghozi Alkatiri** · 05-24-2013, 09:31 AM

=right(trim(substitute(sumproduct((a1:j1)*(10^((columns(a1:j1)-column(a1:j1))))),0,"")),3)

**ChemistB** · 05-24-2013, 09:48 AM

Another arrayed formula

=IFERROR(OFFSET(A1,0,MATCH(2, 1/(A1:J1<>0))-3),"") & IFERROR(OFFSET(A1,0,MATCH(2, 1/(A1:J1<>0))-2),"") & IFERROR(OFFSET(A1,0,MATCH(2, 1/(A1:J1<>0))-1),"")

Hmmmm, seems there's more than one way to solve an Excel problem. Who'd a thunk?