# Thread: Sort by the code

1. ## Sort by the code

I'm sorting the spreadsheet the column ordered by date but I want to order by the code, R1 thru R5000, and the sorting does not appeared as I meant. For example, I want to sort the number R1, R2, R3......R5000, but it sorted as R1, R10, R100, R1000, then R1001, R1002,..., then R101, R1010, R1011,..... I think it's easy if I can separate letter and number but I don't know how to separate. Can anyone help?

2. Soochun,

Welcome to the Lounge as a new poster!

You'll need to insert a "Helper Column" next to the row to be sorted.
Enter this formula in the first row of the inserted column: =VALUE(RIGHT(A1,LEN(A1)-1)).
Of course you'll change the A1's to the appropriate column.
Copy the formula down the column.
Sort on the new column.
If no longer needed delete the helper column.
Before -------------------------------- After
Sort on Col A.JPGSort on Col B.JPG

3. Thank you for your help. It separates the number and letter but sorting the number is still not in order. I want to sort in order of 1,2,3,4....5000, but it's order is; 1, 10, 100, 1000, 1001-1009, 101, 1010-1019, 102, 1020-1029, 103, 1030-1039, .... How can I sort the number in order?

4. Soochun,

Did you copy the formula exactly?
If you look at the two graphics you'll see it does sort correctly. If it is sorting the way you state it's most likely because you didn't include the VALUE portion of the formula or you're sorting on the column with the R's vs the helper column w/just the numbers.

I just ran a test of R1-R5000 and it works correctly.

5. Yes. It worked very well. After the separation with letter and number, I just sort directly. But When I paste value then sort, it worked perfectly fine. Thank you so much.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•