List specific number

[Tom.Jones]

Howdy,

In column A is a list of numbers.
What formula should I use to list the first only 7 numbers in column A, whose last digit is 8.

Thank you.

 

[Jon von der Heyden]

Hi

If your values are in A1:A100:

=INDEX(A1:A100,SMALL(IF(RIGHT(A1:A100,1)="8",ROW(A1:A100)),1),1)

And confirm with control + shift + enter

Change the 1 to the nth occurrence that you desire.

Or you could occupy 7 cells in a column and use:

=INDEX(A1:A100,SMALL(IF(RIGHT(A1:A100,1)="8",ROW(A1:A100)),{1;2;3;4;5;6;7}),1)

(also with control + shift + enter)

 

[Tom.Jones]

Hi,

Thanks for reply.

I need first 7 number (not sorted) only.

eg.
column A

142578
2142145
2001248
18
142776
124757
1548
111
12488
124777
20020
457808
198
1348
16588
20147
2013258

And I need only first 7 (no more if drag down formula)

Column B

142578
2001248
18
1548
12488
457808
198
1348

Thanks.

 

[Jon von der Heyden]

Hi

Using the second formula that I suggested.

Excel 2010
[Table="width:, class:head"][tr=bgcolor:#888888][th]_Row\^Col[/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]Nums[/td][td]Results[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

142578​

[/td][td]

142578​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

2142145​

[/td][td]

2001248​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

2001248​

[/td][td]

18​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

18​

[/td][td]

1548​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]

142776​

[/td][td]

12488​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]

124757​

[/td][td]

457808​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]

1548​

[/td][td]

198​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]

111​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]

12488​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]

124777​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td]

20020​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]

457808​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td]

198​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

15​

[/td][td]

1348​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

16​

[/td][td]

16588​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

17​

[/td][td]

20147​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

18​

[/td][td]

2013258​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Select: B2:B8
Enter: =INDEX(A1:A18,SMALL(IF(RIGHT(A1:A18,1)="8",ROW(A1:A18)),{1;2;3;4;5;6;7}),1)
Press: Control+Shift+Enter

Results per the above.

The key is that you must select all 7 cells (B2:B8) first.

Note - your result example shows 8 results, not 7.

Hope this helps.

 

[Tom.Jones]

Thank you very much.

If someone can help me with a VBA code.

 

[Fluff]

How about

Sub FirstSeven()
   Dim cl As Range
   Dim i As Long
   
   For Each cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      If Right(cl.Value, 1) = 8 Then
         i = i + 1
         Range("B" & i).Value = cl.Value
         If i = 7 Then Exit For
      End If
   Next cl
End Sub

 

[Tom.Jones]

Fluff, thank you very much.

 

[Fluff]

You're welcome

 

[My Aswer Is This]

Just so you will know next time.
Your original post said:

What ​

formula​

should I use to list the first only 7 numbers in column A, whose last digit is 8.

A Vba script is not considered a Formula.
​

 

[Tom.Jones]

Just so you will know next time.
Your original post said:

What ​

formula​

should I use to list the first only 7 numbers in column A, whose last digit is 8.

A Vba script is not considered a Formula.
​

Yes. I ask first time for a formula and get formula from Jon,
then I considered to not open another post for same things and ask for VBA code.
I hope I did not upset anyone except you. And yes, I also know that a formula is not a VBA code.