1. I have a spreadsheet, which we have recorded a stock count. My problem is sorting into part number order. Say I have 1A17, 1A2, 1A36, 1A125, 1A56. When I sort, they are coming out as

1A125
1A17
1A2
1A36
1A56

And not

1A2
1A17
1A36
1A56
1A125

as I hoped. Is there a way of getting round this? Or will I just have to live with it? At the moment, the spreadsheet just contains 1 number range (1A), but future spreadsheets could contain other ranges (2B, 2C,....)

2. Add a column which removes the "1A" section of the part 'description' (it is not a part 'number', and that's your problem) and converts the result to a number, then sort on that.

The first operation is simple using string manipulation functions. The second is most easily done by multiplying the result by 1, which forces Excel to treat the result as a number.

3. Assume the part numbers start in Cell A2, try this formula in some other column beginning in Row 2 and copy down.......
=VALUE(RIGHT(A2,LEN(A2)-2))

Highlight all the data to be sorted and sort on the new column.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•