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

#### JonesyUK

##### Board Regular
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

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### 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:
VBA Code:
``````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:
Excel Formula:
``=FindNth(A1,5)``

#### JonesyUK

##### Board Regular
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

##### Board Regular

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:
Excel Formula:
``=FindNth(A1,A2)``

#### Fluff

##### MrExcel MVP, Moderator

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’)

+Fluff v2.xlsm
ABCDEFGHIJKLMNO
15
2405240108558022
358524010855811220156
41085240108558022009
Data
Cell Formulas
RangeFormula
A2:A4A2=INDEX(B2:X2,AGGREGATE(14,6,(COLUMN(B2:X2)-COLUMN(B2)+1)/(B2:X2>0),\$A\$1))

alz

#### JonesyUK

##### Board Regular
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

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

#### Attachments

• Formula.JPG
20.6 KB · Views: 5

#### 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

##### Board Regular
Using Office 365 (it says version 2008??)

Replies
1
Views
81
Replies
10
Views
533
Replies
3
Views
216
Replies
1
Views
99
Replies
10
Views
181

1,129,730
Messages
5,638,031
Members
417,000
Latest member
JasonWilliam

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back