up vote 0 down vote favorite
share [g+] share [fb]

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?

link|improve this question
feedback

1 Answer

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?

link|improve this answer
2  
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
feedback

Your Answer

 
or
required, but never shown
discard

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