| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting by last digits in cell
Hello,
I am trying to sort a column with hundreds of account numbers and would like to sort by the last 4 digits instead of the first digits. Is there a way to do this? Any help would be appreciated! Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sorting by last digits in cell
Say the data is in column A. In B1 enter:
and copy down. Then sort cols A & B by B For example: 856737 6737 236104 6104 825902 5902 221990 1990 991982 1982 267139 7139 307127 7127 549995 9995 762302 2302 194121 4121 172754 2754 200741 0741 365828 5828 538024 8024 483366 3366 178887 8887 716653 6653 646675 6675 283394 3394 371017 1017 791337 1337 863748 3748 707332 7332 976701 6701 515423 5423 390859 0859 640701 0701 601498 1498 495380 5380 654840 4840 will become: 640701 0701 200741 0741 390859 0859 371017 1017 791337 1337 601498 1498 991982 1982 221990 1990 762302 2302 172754 2754 483366 3366 283394 3394 863748 3748 194121 4121 654840 4840 495380 5380 515423 5423 365828 5828 825902 5902 236104 6104 716653 6653 646675 6675 976701 6701 856737 6737 307127 7127 267139 7139 707332 7332 538024 8024 178887 8887 549995 9995 -- Gary''s Student - gsnu200907 "Julie" wrote: Hello, I am trying to sort a column with hundreds of account numbers and would like to sort by the last 4 digits instead of the first digits. Is there a way to do this? Any help would be appreciated! Thank you. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sorting by last digits in cell
--Use a helper column to the right. Suppose you have data in ColA In cell B1
=RIGHT(A1,1) and copy down.. --Select both columns sort by column B If this post helps click Yes --------------- Jacob Skaria "Julie" wrote: Hello, I am trying to sort a column with hundreds of account numbers and would like to sort by the last 4 digits instead of the first digits. Is there a way to do this? Any help would be appreciated! Thank you. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sorting by last digits in cell
In B1, enter:
=right(a1,4) (to return the last 4 characters as text) or =--(right(a1,4) (to return the last 4 characters (which must be numeric) as a real number or =mod(a1,10000) (if the values are really numbers) Gary''s Student wrote: Say the data is in column A. In B1 enter: and copy down. Then sort cols A & B by B For example: 856737 6737 236104 6104 825902 5902 221990 1990 991982 1982 267139 7139 307127 7127 549995 9995 762302 2302 194121 4121 172754 2754 200741 0741 365828 5828 538024 8024 483366 3366 178887 8887 716653 6653 646675 6675 283394 3394 371017 1017 791337 1337 863748 3748 707332 7332 976701 6701 515423 5423 390859 0859 640701 0701 601498 1498 495380 5380 654840 4840 will become: 640701 0701 200741 0741 390859 0859 371017 1017 791337 1337 601498 1498 991982 1982 221990 1990 762302 2302 172754 2754 483366 3366 283394 3394 863748 3748 194121 4121 654840 4840 495380 5380 515423 5423 365828 5828 825902 5902 236104 6104 716653 6653 646675 6675 976701 6701 856737 6737 307127 7127 267139 7139 707332 7332 538024 8024 178887 8887 549995 9995 -- Gary''s Student - gsnu200907 "Julie" wrote: Hello, I am trying to sort a column with hundreds of account numbers and would like to sort by the last 4 digits instead of the first digits. Is there a way to do this? Any help would be appreciated! Thank you. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sorting by last digits in cell
Hi,
You may try this. Download and install the following add in http://www.download.com/Morefunc/300...-10423159.html. Thereafter perform the following steps: 1. Suppose your data is in range E16:E17 2. Select H16:h17 and then array enter (Ctrl+Shift+Enter) the following formula =VSORT(E16:E17,RIGHT(E16:E17,4),1) Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Julie" wrote in message ... Hello, I am trying to sort a column with hundreds of account numbers and would like to sort by the last 4 digits instead of the first digits. Is there a way to do this? Any help would be appreciated! Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| number digits 1-6 not sorting properly | Excel Discussion (Misc queries) | |||
| sorting a series of nos. on basis of digits | Excel Discussion (Misc queries) | |||
| sorting numbers with an irregular number of digits | Excel Discussion (Misc queries) | |||
| How?-Sorting last digits and not the first | Excel Discussion (Misc queries) | |||
| Sorting data by last to digits | Excel Worksheet Functions | |||
Linear Mode
