CODEWITHSUNDEEP
excelexcel-2007spreadsheet
user5389574
user5389574

Reputation: 1

How do I fix the ordering in Microsoft excel?

When I try to sort a column, the sorting isn't exactly right. The content in the column contains text and numbers.

Here's an excel file where the ordering is wrong: https://www.dropbox.com/s/grbg0ic2r5fylpw/sample.xls?dl=0

Can someone help me fix the problem?

Thanks

Upvotes: 0

Views: 58

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

  • Select the data in the column A
  • Use the Data/Text to columns wizard to split the original data using space as the delimiter, but put the destination = $B$1 at Step 3 so as to leave the original intact.
  • Sort on columns B, C, D, etc, in that order
  • Delete all except column A

If this will need to be done repetitively, record a macro while you do these steps, then edit the macro to recognize the appropriate ranges automatically.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152605

Excel sorts text fields Character by Character so text wise "11" come before "2". So you need to add leading 0 to the numbers. "002" comes before "011"

To do that:

In an empty column put the following formula in the first row:

=LEFT(A1,SEARCH("}}}",SUBSTITUTE(A1," ","}}}",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) & TEXT(--TRIM(MID(A1,SEARCH("}}}",SUBSTITUTE(A1," ","}}}",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),999)),"000")

Then copy down.

enter image description here

Then copy the new column and paste just the values on the old column. Then your sort should work.

If you want to keep the original formatting then leave the new column and sort on the new column instead. You can hide the new column.

enter image description here

Upvotes: 1

Caratacus012
Caratacus012

Reputation: 1

Seem like there are some formatting issues in the data. I selected all your data, opened a new spreadsheet, copied and clicked on paste special. I choose values and numbers. This brings back the data without any formatting in them. I was then able to sort this properly.

Upvotes: 0

Related Questions