Find nth non-zero number in range, from right to left..

[JonesyUK]

Hi All,

Please can you suggest a formula to return the nth non-zero number in a row of data, starting from the right.

Example: the 5th non-zero number in the following string, starting from the right would be 108:

52, 40, 108, 5, 58, 0, 22

Thanks for your help!

 

[Joe4]

Wouldn't the 5th non-zero value from the right in that string actually be 40, not 108 (seems that you accidentally counted the zero value!)?

How exactly do you intend to do use this?
Do you have a worksheet of numeric strings?
How/where is it determined which value you want (5th, 3rd, 1st, etc)?

Just trying to figure out if you just want a procedure to run to fix up some data, or if you are more looking for a User Defined Function you can use on a string anytime you want.

 

[Joe4]

Here is a User-Defined function I came up with to do this:

Function FindNth(str As String, n As Integer) As Double

    Dim numArr() As String
    Dim counter As Integer
    Dim i As Integer
    Dim val As Double
    
'   Split numbers by comma and store in array
    numArr = Split(str, ",")
    
'   Loop through values in array backwards
    For i = UBound(numArr) To LBound(numArr) Step -1
'       Get value
        val = numArr(i)
'       If value is not zero, add one to counter
        If val <> 0 Then
            counter = counter + 1
        End If
'       See if counter matches the number we are looking for
        If counter = n Then
            FindNth = val
            Exit Function
        End If
    Next i
    
End Function

So after pasting this code in a generic VBA module in your workbook, you can use it on your worksheet or in VBA like any other Excel function.

So if your string was in cell A1, and you wanted the 5th value, you would simply use this formula:

=FindNth(A1,5)

 

[JonesyUK]

Wouldn't the 5th non-zero value from the right in that string actually be 40, not 108 (seems that you accidentally counted the zero value!)?

How exactly do you intend to do use this?
Do you have a worksheet of numeric strings?
How/where is it determined which value you want (5th, 3rd, 1st, etc)?

Just trying to figure out if you just want a procedure to run to fix up some data, or if you are more looking for a User Defined Function you can use on a string anytime you want.

oh yikes, yes you're right. Sorry! I accidentally counted the zero....

 

[JonesyUK]

oh yikes, yes you're right. Sorry! I accidentally counted the zero....

A fixed cell, say A1 would contain the number for n. Thanks.

 

[Joe4]

A fixed cell, say A1 would contain the number for n. Thanks.

See the solution a posted up above.

So, if the string was in cell A1, and the number for n was in cell A2, then the formula would look like:

=FindNth(A1,A2)

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

With a formula, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	5
2	40	52	40	108	5	58	0	22
3	58	52	40	108	5	58	11	22	0	15	6
4	108	52	40	108	5	58	0	22	0	0	9
Data

Cell Formulas
Range	Formula
A2:A4	A2	=INDEX(B2:X2,AGGREGATE(14,6,(COLUMN(B2:X2)-COLUMN(B2)+1)/(B2:X2>0),$A$1))

 

[JonesyUK]

Hi All,

Please can you suggest a formula to return the nth non-zero number in a row of data, starting from the right.

Example: the 5th non-zero number in the following string, starting from the right would be 108:

52, 40, 108, 5, 58, 0, 22

Thanks for your help!

I also made a mistake with the description, it shouldn't say string, it should say "Range". Please see image. Thanks.

 

[Joe4]

I also made a mistake with the description, it shouldn't say string, it should say "Range". Please see image. Thanks.

You have had two solutions posted which should work for you.
Have you tried either one?

 

[JonesyUK]

Using Office 365 (it says version 2008??)