Excel Data Sorting Question

[wesleythomas]

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.

 

[timorrill]

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:

=VALUE(RIGHT(B1,LEN(B1)-FIND("-",B1)))

Book1
	A	B	C	D
1	100	CSJ-100
2	1001	CSJ-1001
3	1002	CSJ-1002
4	1003	CSJ-1003
5	1004	CSJ-1004
6	101	CSJ-101
7	102	CSJ-102
8	103	CSJ-103
9	104	CSJ-104
10	105	CSJ-105
11	106	CSJ-106
12	200	CSJ-200
13	201	CSJ-201
14	202	CSJ-202
15	203	CSJ-203
16	204	CSJ-204
Sheet1

Then sort by numeric value.

 

[wesleythomas]

thank you so much! worked like a charm.