Excel Data Sorting Question

wesleythomas

New Member
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

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.

thank you so much! worked like a charm.

Replies
2
Views
271
Replies
9
Views
564
Replies
4
Views
425
Replies
9
Views
393
Replies
1
Views
450

1,217,454
Messages
6,136,734
Members
450,025
Latest member
Beginner52

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.

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

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