Sort by numbers problem

[roger_home]

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 occasionall
we add a A or a X to the end of the number (i.e., 772A, 1001X). No
when I do a sort by number, these go the the bottom. Any way to kee
them in order by number

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.