How do you sort columns of number/text mix in Excel?

Make the numbers all 2 digits….

example-01
example-02
example-03
example-10
example-11
example-12

Then it will sort correctly.

@ETpro: ”...what humans would consider numerical order”

It’s actually sorting alphabetically, so it makes sense. 1, 10, 11, etc…

Step ONE save a copy of the file so you can recover if you goof up

Then, here are two options

1) If the word “example” is in every cell
Choose column A by clicking on the A, so the following step only affects that column
Choose Edit->Replace (or control H in Windows)
Find “example-
Replace with nothing (literally nothing, don’t put anything in the Replace box
You column now says 1, 10, 11, 12, 13, 14, etc

2) If you don’t wish to wipe out “example”
Create a new column to the right of that column (let’s call it column A)
Select column A
Choose Data tab
Choose Text to Columns
Select Delimited
Choose Next
In the box labeled Delimiters->Other enter a dash ”-”
Choose Next

Now you have column A which says, example, example, example
And Column B says 1. 10. 11. 12,. etc

Sort on Column B

@tom_g No can do. It’s a massive database. The CSV has 110,000 rows and 60 columns of data, of which this fate is one column. I have to break the CSV into two parts or 32 bit Excel can’t even open it without truncating. It has a 65,000 row limit. I am definitely not going through it to add leading zeros.

@jaytkay I follow where you are going. Unfortunately, I just used the word “example” as an ecample. There are 15 different styles of custom blinds and shades. Each has its own text prefix, followed by 2 numbers showing a width and length respectively in inches. So the ID of a real product would be “wood2inch-36–48”. The products are made in dimensions ranging from 10×10 to 96 wide by 108 long. So that’s a matrix of over 8,000 sizes.

It’s sorting according to machine collating sequence for an alphanumeric field, which is left to right just as if it were a word, and it’s never going to come out any other way as long as you have the alpha and numeric portions in the same field and you don’t use lead zeroes.

If this were my database to maintain, I would either (a) educate users to read numbers from left to right—all 1’s precede all 2’s, etc.—or (b) go through the necessary pain to separate the alpha and numeric portions or add lead zeroes. (a) is easier in the short term and (b) is more sensible for the long term.

This should have been considered when the database was designed and the product numbering system was devised. I was educated in this collating sequence logic before I was ever set to work creating master product files, and we worked out ways around it for a complicated mix of items.

One real option is to add a column called ‘sequence’ that represents the actual sort order and sort on that instead of on the product number.

@Jeruba You are dead right it should have been thought of ahead of time. Unfortunately, I’m inheriting something that has existed for years, and only now do they want to put it on the web, requiring I build a CSV file of the resitting SKUs so they still work with their internal inventory and factory order system. I suppose I could split it into text prefix column plus two columns for the two size modifiers as pure numbers. A pretty simple Excel formula should accomplish that. Then I could concatenate the 3 rows into one SKU row using another formula. That should allow a clean sort.

Even at that, @ETpro, you’re going to need lead zeroes or you’ll get the same result.

@Jeruba Not so. I just tested. If I set the dimension columns Using Format >> Cells X to Number instead of the default General and make sure to set Decimal Places to 0 instead of the default 2, they sort correctly even without leading zeros.

Ah, yes, in pieces. As long as the numeric fields are right-justified (which does in effect add lead zeroes) or simply read as numeric. I was thinking of the concatenated fields: if you sorted them after concatenation, you’re back with the original problem.

@Jeruba Fortunately, I can leave the sorting fields there just as controls. If I give each one a name in the header that matches no variable in the online database, I can upload the CSV and the database will simply skip over those columns.