List specific number

Tom.Jones

Well-known Member
Joined
Sep 20, 2011
Messages
506
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Mobile
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.
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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)
 
Upvote 0
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.
 
Last edited:
Upvote 0
Hi

Using the second formula that I suggested.

Excel 2010
Row\Col
A
B
1
NumsResults
2
142578
142578​
3
2142145
2001248​
4
2001248
18​
5
18
1548​
6
142776
12488​
7
124757
457808​
8
1548
198​
9
111
10
12488
11
124777
12
20020
13
457808
14
198
15
1348
16
16588
17
20147
18
2013258
Sheet: Sheet1

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.
 
Upvote 0
Thank you very much.

If someone can help me with a VBA code.
 
Upvote 0
How about
Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top