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

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

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

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

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