Tech Support banner

Status
Not open for further replies.
1 - 7 of 7 Posts

·
Registered
Joined
·
136 Posts
Discussion Starter #1
Hey guys,

Is there a way to sort numeric filing numbers in Excel by the "last two digits"?
The company where I work uses a 7-digit numeric file for clients and I would like to put these clients in numerical order by the "last two digits"
Example:
821-45-20
321-44-22

Excel sorts them like this:
Example:
321-44-22
821-45-20

Can someone help?

Thanks.
 

·
Superhuman Computer
Joined
·
1,632 Posts
The easiest method for diong this would be to make another column of data which just contains the last two digits of the cell. Assuming the data starts in A1 and goes down from there, put the following formula in the same row as the first record (ie row 1):

Code:
=Right(A1,2)
now auto fill this down to the bottom of the column, and you will have a column of the last two digits in the filing number. Select the entire area you want sorted (including the new column) and sort by the new column. It should now come up with a selection saying: sort numbers and text separately or sort anything that looks like a number as a number. Select sort anything that looks like a number as numbers, and you should have your list sorted by the last two digits.
 

·
Registered
Joined
·
136 Posts
Discussion Starter #3
Thanks ReeKorl,

I had tried what you mentioned, however, I had over 1,000 clients and didn't want to type in the last two digits on the new column (it would be too much work). I also would've had to delete the last two digits on the column where the whole number is.

We are having a Query Writer to work on this. I just thought I could do it in Excel.

Thanks.
 

·
Superhuman Computer
Joined
·
1,632 Posts
Nono, you misunderstand me. The formula posted will show the last two digits if you type that into the top cell. simple auto-fill that down and it will show the last two digits for all lines, on their line. no modification is necessary to the original value, and the new value is only used for the sort.

Try it out. Type ten of the numbers you have already into cells a1 to a10. type the formula as posted into b1, and auto-fill down to b10. If you now sort on the B column, it will sort the A column into numeric order on the last two digits.

After sorting, you can actually delete the new column made for this purpose, or just ignore it.
 

·
Registered
Joined
·
136 Posts
Discussion Starter #5
Yeah, I misunderstood. Let me try what you mentioned.

I'll let you know.

Thanks
 

·
Registered
Joined
·
136 Posts
Discussion Starter #6
Great, it worked perfectly!

Thanks a lot for the tip ReeKorl. This is going to help me a lot.
 

·
Superhuman Computer
Joined
·
1,632 Posts
Excellent, glad to help :grin:
 
1 - 7 of 7 Posts
Status
Not open for further replies.
Top