| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 101118 | 2009-07-02 05:45:00 | XL Sort problem | Woody (710) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 788098 | 2009-07-02 05:45:00 | I have a spreadsheet with a number of columns which I wish to sort on. All OK until the column with Street numbers when order correctly except a number with and alphabetic suffix eg 41A. Straight numerics order sorrectly but when the 41A or 57C (eg) they appear out of order and this also fouls up the succeeding valus. Is this a format problem with the number column or something more complicated? Any suggestions would be appreciated Thanks |
Woody (710) | ||
| 788099 | 2009-07-02 06:44:00 | A quick response before I think of a more elegant solution: If I was doing that I'd copy the numbers to another column with an formula that translated any right hand characters higher than ascii 64 to if ascii(righthandcharacter)>64 then take (ascii(character)-64)/100 and add that to the whole number. Sort on that result rather than the street number That would give 40A 40B as 40.01 40.02. Sorry I haven't got Excel readily to hand to quote exact formulae but that should give you a hint. You don't need to display or print that column, just sort on it. And if you have mixed upper and lower case letters you'd need to subtract 32 from those over 96 to get them in the right order (assuming that you don't mind 40a 40A in sequence. | coldot (6847) | ||
| 788100 | 2009-07-02 09:49:00 | As suggested by coldot, try using something like this in a helper column and sort on this column instead (assuming the house number starts in cell A1) : =TEXT(A1,"@") You will get a sort warning, but choose the option to sort numbers and text separately. This won't take account of units where the data looks like this: 3/8 etc. It will sort based on the 3 and not the 8. Andrew |
andrew93 (249) | ||
| 788101 | 2009-07-02 22:59:00 | Thanks heaps guys. Great help Cheers |
Woody (710) | ||
| 788102 | 2009-07-03 00:50:00 | Main trap you've fallen into, is having two pieces of data in one field/column, ie Street No and Flat No. Could be worth fixing? Once you start on the 'work around' track, it never seem to end. |
donread (6401) | ||
| 1 | |||||