Excel Data Sorting Question

wesleythomas

New Member
Joined
Sep 20, 2006
Messages
8
I update a website with a data entry tool called MyEzShopPro and recently had our host send me an Excel sheet of our 10,000 plus online items. I am using the sheet to keep track of used SKU numbers as well as check for any spelling errors.

The spreadsheet is aranged by our SKU numbers which look like this (CSJ-1100). Is there any way I can get the cells to arrange in proper numerical order? I'm having the common problem of it going CSJ-100, CSJ-1001, CSJ-1002 as opposed to the proper CSJ-100, CSJ-101, CSJ-102.

I am currently sorting through line by line and cutting and pasting them in to a new sheet and its a bit tedious. Any help would be greatly appreciated.

Thanks.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

timorrill

Well-known Member
Joined
Sep 20, 2006
Messages
528
A quick and dirty solution to your problem (should save a little time, at least):

Insert a column before the product number and extract the numeric portion of the product number (see below). The formula to get the value of the numeric portion is:

Code:
=VALUE(RIGHT(B1,LEN(B1)-FIND("-",B1)))
Book1
ABCD
1100CSJ-100
21001CSJ-1001
31002CSJ-1002
41003CSJ-1003
51004CSJ-1004
6101CSJ-101
7102CSJ-102
8103CSJ-103
9104CSJ-104
10105CSJ-105
11106CSJ-106
12200CSJ-200
13201CSJ-201
14202CSJ-202
15203CSJ-203
16204CSJ-204
Sheet1


Then sort by numeric value.
 

Forum statistics

Threads
1,136,654
Messages
5,677,012
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top