Sorting Issue I have a customer who\'s information I copy form their website int

Question

Sorting Issue

I have a customer who's information I copy form their website into a spreadsheet, I need to sort the information, but when I do, it sorts differently than I expect to see it sorted, any help would be appreciated, here is what it looks like when I sort and what I want it to look like when I sort.

this is how it sorts

565939-10

565939-100

565939-110

565939-20

565939-30

565939-40

565939-50

565939-60

565939-70

565939-80

565939-90

this is how I want it to sort

565939-10

565939-20

565939-30

565939-40

565939-50

565939-60

565939-70

565939-80

565939-90

565939-100

565939-110

Thanks !!

Explanation / Answer

Hi..

try

Assuming that the range starts from A1 and the first part of these serial numbers could vary::

B1=LEFT(A1,FIND("-",A1))&TEXT(RIGHT(A1,LEN(A1)-FIND("-",A1)),"000")

Then sort both A and B columns by B in ascending order.

Or if you want it done dynamically without using the DATA>SORT menu/icon

B1 =INDEX($A$1:$A$11,MATCH(SMALL((LEFT($A$1:$A$11,FIND("-",$A$1:$A$11)-1)&TEXT(RIGHT($A$1:$A$11,LEN($A$1:$A$11)-FIND("-",$A$1:$A$11)),"000"))*1,ROWS($A$1:A1)),(LEFT($A$1:$A$11,FIND("-",$A$1:$A$11)-1)&TEXT(RIGHT($A$1:$A$11,LEN($A$1:$A$11)-FIND("-",$A$1:$A$11)),"000"))*1,0))

CTRL+SHIFT+Enter

copy down

565939-10 =LEFT(A1,FIND("-",A1))&TEXT(RIGHT(A1,LEN(A1)-FIND("-",A1)),"000") 565939-20 =LEFT(A2,FIND("-",A2))&TEXT(RIGHT(A2,LEN(A2)-FIND("-",A2)),"000") 565939-30 =LEFT(A3,FIND("-",A3))&TEXT(RIGHT(A3,LEN(A3)-FIND("-",A3)),"000") 565939-40 =LEFT(A4,FIND("-",A4))&TEXT(RIGHT(A4,LEN(A4)-FIND("-",A4)),"000") 565939-50 =LEFT(A5,FIND("-",A5))&TEXT(RIGHT(A5,LEN(A5)-FIND("-",A5)),"000") 565939-60 =LEFT(A6,FIND("-",A6))&TEXT(RIGHT(A6,LEN(A6)-FIND("-",A6)),"000") 565939-70 =LEFT(A7,FIND("-",A7))&TEXT(RIGHT(A7,LEN(A7)-FIND("-",A7)),"000") 565939-80 =LEFT(A8,FIND("-",A8))&TEXT(RIGHT(A8,LEN(A8)-FIND("-",A8)),"000") 565939-90 =LEFT(A9,FIND("-",A9))&TEXT(RIGHT(A9,LEN(A9)-FIND("-",A9)),"000") 565939-100 =LEFT(A10,FIND("-",A10))&TEXT(RIGHT(A10,LEN(A10)-FIND("-",A10)),"000") 565939-110 =LEFT(A11,FIND("-",A11))&TEXT(RIGHT(A11,LEN(A11)-FIND("-",A11)),"000")

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.