Isolating all cells by last digit

  • Thread starter Thread starter John R. Youngman
  • Start date Start date
J

John R. Youngman

Hi. I'm using Excel 2003 and have a small spreadsheet (one column) with
thousands of entries. I need to isolate or group entries based on the last
digit.

Here's what I have:

2566
1231
5876
2540
4561
2960

Here's what I would like:

2540
2960
1231
4561
5876
2566

Any ideas on how to do this?

TIA

John
 
John,

I think you'll need a helper column. Presuming your stuff is in column A,
put either of these in another column, and copy down with the fill handle:

=MOD(A1, 10)
=RIGHT(A1, 1)

Now sort the table on that column. You can hide it when you don't need it.
 
Add a helper column B with the formula =RIGHT(TEXT(A1,0),1) copied down.
Sort with it as the first sort key.
 
One way:

In an adjacent "helper" column enter

=MOD(A2,1)

and copy down.

Sort both columns on the helper column.

Note that your example is inconsistent in sorting with 5876 above 2566.
 
Thanks, Earl. Both methods worked great!

John


Earl Kiosterud said:
John,

I think you'll need a helper column. Presuming your stuff is in column A,
put either of these in another column, and copy down with the fill handle:

=MOD(A1, 10)
=RIGHT(A1, 1)

Now sort the table on that column. You can hide it when you don't need it.
Click to expand...
 
Thanks. Worked just right.

John


JWolf said:
Add a helper column B with the formula =RIGHT(TEXT(A1,0),1) copied down.
Sort with it as the first sort key.
Click to expand...
 
Thanks for the quick reply. You're right about my example being
inconsistent on the last two entries. Your answer was very similar to
another:

=MOD(A1,10) compared to your =MOD(A2,1)

I know the "A1" is a cell reference; why did you recommend "1" instead of
"10" ??

Just curious.

Thanks again.

John
 
Back
Top