How do you sort in Excel by 1, 1a, 1b, 1c, ...2, 2a?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to sort a list of numbers in excel but I want 1A to be in between 1 &
2. Hos do I do that? I tried setting up a custom list, but no luck.
 
To get the sort order you want all the items must be text. For example,
select A1:A3. Use Format, Cells and select Text as the number format.
Enter 1, 2, 1a in the cells and then click the Sort button on the Standard
toolbar. The order should sort to 1, 1a, 2.

--
Jim Rech
Excel MVP
|I want to sort a list of numbers in excel but I want 1A to be in between 1
&
| 2. Hos do I do that? I tried setting up a custom list, but no luck.
 
My solution:

="'"&REPT("0",2-LEN(MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))))&A1

Remember to enter with CTL+SHIFT+ENTER and then copy to other cells.

1 will be changed to '001,1A to '01A and 23 to '023, etc.

I think this is much easier.

Regards

Fair Cape
 
Back
Top