Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sort by numbers problem
My company has a 3 or 4 digit job number on all its jobs (i.e., 872, 944, 1015)...when I do a sort by number, all is fine. But occasionally we add a A or a X to the end of the number (i.e., 772A, 1001X). Now when I do a sort by number, these go the the bottom. Any way to keep them in order by number? -- roger_home ------------------------------------------------------------------------ roger_home's Profile: http://www.excelforum.com/member.php...o&userid=16434 View this thread: http://www.excelforum.com/showthread...hreadid=385270 |
#2
|
|||
|
|||
The values with a character at the end are being sorted as TEXT, not numbers.
The only way I could get it to work was to include a ' before the number. I'm sure there's a more elegant way. "roger_home" wrote: My company has a 3 or 4 digit job number on all its jobs (i.e., 872, 944, 1015)...when I do a sort by number, all is fine. But occasionally we add a A or a X to the end of the number (i.e., 772A, 1001X). Now when I do a sort by number, these go the the bottom. Any way to keep them in order by number? -- roger_home ------------------------------------------------------------------------ roger_home's Profile: http://www.excelforum.com/member.php...o&userid=16434 View this thread: http://www.excelforum.com/showthread...hreadid=385270 |
#3
|
|||
|
|||
Alternatively, format the column with the job numbers as text first and enter
the numbers. Again, I'm sure there's a more elegant way out there. "roger_home" wrote: My company has a 3 or 4 digit job number on all its jobs (i.e., 872, 944, 1015)...when I do a sort by number, all is fine. But occasionally we add a A or a X to the end of the number (i.e., 772A, 1001X). Now when I do a sort by number, these go the the bottom. Any way to keep them in order by number? -- roger_home ------------------------------------------------------------------------ roger_home's Profile: http://www.excelforum.com/member.php...o&userid=16434 View this thread: http://www.excelforum.com/showthread...hreadid=385270 |
#4
|
|||
|
|||
Hi,
This could be one approach (there may be more elegant ones that I can't think of). You need a helper column to extract the numeral parts of the job numbers for sorting purposes. Supposing the job numbers are in column A starting at row 2, the formula in row 2 of the helper column would be, =IF(ISTEXT(A2)*1=1,LEFT(A2,LEN(A2)-1)*1,A2) Now you could sort the spreadsheet by this helper column. If it is possible that you could have entries starting with the same numbers followed by different literal characters for different jobs (e.g., 772, 772A, 772X....), then you need two helper columns that would become the sorting criteria. =IF(ISTEXT(A2)*1=1,LEFT(A2,LEN(A2)-1)*1,A2), and =IF(ISTEXT(A2)*1=1,RIGHT(A2,1),"") Now you would sort the spread sheet first by the helper column 1 and then by helper column 2. Hope this helps. Regards. B.R.Ramachandran "roger_home" wrote: My company has a 3 or 4 digit job number on all its jobs (i.e., 872, 944, 1015)...when I do a sort by number, all is fine. But occasionally we add a A or a X to the end of the number (i.e., 772A, 1001X). Now when I do a sort by number, these go the the bottom. Any way to keep them in order by number? -- roger_home ------------------------------------------------------------------------ roger_home's Profile: http://www.excelforum.com/member.php...o&userid=16434 View this thread: http://www.excelforum.com/showthread...hreadid=385270 |
#5
|
|||
|
|||
You could add spaces after the numbers that have only digits. Word's Search
and Replace is a useful tool for this if you use wildcards. "roger_home" wrote: My company has a 3 or 4 digit job number on all its jobs (i.e., 872, 944, 1015)...when I do a sort by number, all is fine. But occasionally we add a A or a X to the end of the number (i.e., 772A, 1001X). Now when I do a sort by number, these go the the bottom. Any way to keep them in order by number? -- roger_home ------------------------------------------------------------------------ roger_home's Profile: http://www.excelforum.com/member.php...o&userid=16434 View this thread: http://www.excelforum.com/showthread...hreadid=385270 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
minus numbers causing a problem | New Users to Excel | |||
sort numbers based on initial digit. | Excel Discussion (Misc queries) | |||
Sort Problem | Excel Discussion (Misc queries) | |||
sort + & - numbers together | Excel Worksheet Functions | |||
How do I reference and sort a range of numbers in Excel 97? | Excel Worksheet Functions |