another problem - sorting numbers

[markster]

I have another problem. We use reference numbers at work like this

E/2000/1
E/2000/2
E/2000/3

When sorting it always sorts like this

E/2000/1
E/2000/100
E/2000/1000
E/2000/1001
E/2000/1002
E/2000/1003
E/2000/1004
E/2000/2
E/2000/200

instead of

E/2000/1
E/2000/2

etc etc

Is there a way of resolving this problem so it sorts as 1,2,3 etc instead of lumping together all the numbers with 1, 2 etc - hope this makes sense.
Markster

[JayKay]

Hi Markster

The problem is that Excel is seeing the entries as text. You could try 'extracting' the 2 numeric elements using the 'mid' and 'int' function and then sort using these values. Assuming that your data is in column A then use

=int(mid(A1, 3,4))

in cell B1 and

=int(mid(A1,8,len(A1)-7)

in cell C1

Copy these formulas down the length of the data and sort by the results in columns B & C.

Cheers

JayKay

[JayKay]

Sorry, missed a bracket off the second formula which should read:

=int(mid(A1,8,len(A1)-7))

Cheers

JayKay

[eliW]

Another way, a little bit tricky:
1) Copy your data to column B and select it
2) From menu choose Data | Text to columns
3) Enter the wizard
4) Choose delimited delimiters other "/" (this will devide your data to 3 fields)
5) Now you can filter on columns B then C then D, and data in column A will be in the right order
Eli

[markster]

Hi JayKay thanks for the formulas but they both return this error #VALUE! any ideas.
PS I'm from Leicester too

[markster]

eliW - Once again it was just what I needed - problem sorted (again) THANKS