Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a list in Excel that i need to sort. But i need to sort on number xx in the following format:

  1. 1234xx123456
  2. 3541xx548135

So if xx in the first row is 68, and the xx in the second row is 45, they would change place. Is there any way of doing this in Excel?

share|improve this question
up vote 8 down vote accepted

Couldn't you just extract the 4th and 5th characters into another cell and sort on that?

B1:   =mid(A1,5,2)

Then sort on column b?

share|improve this answer
Right idea. Use this: =VALUE(MID(A1,5,2)) – Excellll Oct 19 '11 at 13:51
Thanks my original pseudocode included the value part, but was edited out by @AdamV – Paul Oct 19 '11 at 20:56
I agree with Excellll. This should also work: =0+MID(A1,5,2) – Kaze Oct 20 '11 at 2:50
Thank you! It did the work (of course) :-) – Svein Erik Oct 20 '11 at 7:45
Sorry if I mucked anything up translating from pseudo to real Excel. I nearly posted a new answer but wanted you to get the credit for the right idea. 0+ or VALUE() are fine to convert the text to number for "proper" sorting, but if these are always fixed length as two-digit numbers they will sort properly anyway even if left as text, so there's no need really. – AdamV Nov 4 '11 at 14:16

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.