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

JonesyUK

Board Regular
Joined
Oct 11, 2005
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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!
 

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,940
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,940
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 11, 2005
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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.... :oops:
 

JonesyUK

Board Regular
Joined
Oct 11, 2005
Messages
183
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

oh yikes, yes you're right. Sorry! I accidentally counted the zero.... :oops:
A fixed cell, say A1 would contain the number for n. Thanks.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,940
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,632
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
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))
 
  • Like
Reactions: alz

JonesyUK

Board Regular
Joined
Oct 11, 2005
Messages
183
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Formula.JPG
    Formula.JPG
    20.6 KB · Views: 5

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,940
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 11, 2005
Messages
183
Office Version
  1. 365
Platform
  1. Windows
Using Office 365 (it says version 2008??)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top