Is there a way to sort numerically in Excel?

Question

I have data that has already been created, and can be exported into Excel for sorting. The data in the cells is in this form: Blue Widget 1, Blue Widget 2, Blue Widget 3 and so on up to 300. (The spreadsheet lists many, many different types of items, all numbered.) Excel sorts alphabeticaly so it puts them in the following order 1, 10, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 11, 110 etc. I understand that if the data had been created with leading zeros it would sort properly, but that is not the case. Can anyone help please?

Answer 1

Add a column to that table with this function
=VALUE( MID( B2, 12,100))
Assuming that "Blue Widget 1" is in cell B2 and down to the 300 other values
And Assuming that all you list in column B is in this format (12 letters, then the number)
Then fill down this function to the rest of the table
Do a sort to the table BY this new added column
Then you can delete it

Enjoy my profile, I am the VBAXLMan

Answer 2

You have a problem if the items name are of varying length. Hopefully, there is a space followed by the product number which is always joined at the end. There is a way to do it. It involves using table to column function, counta() and offset() function. Email me the sample if you cannot figure it out.

Answer 3

sure, in case you open residing house windows Explorer and pass to the folder containing the info. Then pass to VIEW menu, then %. pick information. you could now go with DATE ACCESSED as a column for instance.

Answer 4

If your excel file is not private and you would like to share it with me, send it to my e-mail and I will be more than happy to try and sort it for you. My e-mail is [e-mail].