excel sorting, mix of letters/numbers

[Brad Johnson]

Fighting an issue in Excel. I have a master database of rooms. Room numbers are the usual mix where main room numbers are all numeric but sub-rooms have an alpha suffix.

Example: Main room is 120. Attached storage room is 120A.

Excel is doggedly treating some cells as text, some as numbers. I've tried group-formatting them to TEXT, GENERAL, and CUSTOM with no luck. It also insists on grouping the sorts into TEXT and NUMBER blocks regardless of what formatting I use.

When I sort, it gives me the choice of A) Sort anything that looks like a number as a number, or B) Sort numbers and numbers stored as text separately. Neither gives satisfactory results, wanting to group cells with numbers only and cells with numeric/alpha room numbers seperately.

Anyone have a way to beat it into submission?

Brad

[MechAg94]

The only brute force way that comes immediately to mind is to strip off the letters into a separate column then do a two tier sort.  Depends on how long the list is.  I will have to think more about that.

[K Frame]

Just off the top of my head, set the cell type to General and throw a hyphen in between the room number and letter.


Or... Move the letter designator to its own column.

[K Frame]

OK, my first suggestion doesn't work.

Adding a new column for the letter designator and doing a custom two-tiered sort as suggest by Mech works.... sort of.

When I did it I got..

120A
120B
120C
120

221A
221B
221

I can't figure out how to get the numeric only room to list first, followed by the alpha numerics.

[dogmush]

The technique posted in the first response on this thread works: https://www.mrexcel.com/board/threads/sort-numbers-with-letters-at-the-end.1174352/

You need the numbers formatted as numbers, and the numbers with suffixs formatted as text but I just ran it through a 32 cell mixed array and it'll work.  It feels kinda clunky though.  It has the benefit that you can add new numbers to the original column (P in the example) and just update the cell range in the 3 forumulas and it will spit out a new sorted list.  Then just hide all but the last sorted column until you need new numbers.

[K Frame]

Ah, so you have to format on a cell-by-cell basis.

Makes sense.

[Brad Johnson]

Everything is already formatted as text. If I format the entire column as numbers, Excel converts them to formulae and fubars the whole thing.

It's not a huge problem, more just a PITA. I thought maybe there was a simple fix.

Brad

[dogmush]

Ah, so you have to format on a cell-by-cell basis.

Makes sense.

That won't do it by itself.  what you need to do is "extract" the suffix letter from the number, add a value for the suffix, and then sort by the combination.  That's why I said it's clunky.

So let's do an example:  Column A has the mixed up room numbers:
7
5
5c
5a
4
4a
1
3
2a
2

If you just type that into Excel it will auto format the numbers with suffixes as text, and the other as numbers.  Then in Cell B1 you type =SORT(A1:A10) (change the cell numbers to your actual cell range) and auto fill it down.  You will then get this:

7       1
5       2
5c     3
5a     4
4      5
4a    7
1      2a
3      4a
2a    5a
2      5c

Sorted ascending, with numbers first and text second.  In cell C1 you type =IF(ISTEXT(B1:B10),VALUE(LEFT(B1:B10,LEN(B1:B10)-1)),B1:B10)  and autofill it down (again, replace the cell arrays with the appropriate numbers).  This will extract the numbers from the cells with suffixes and asign everything a numerical value.  OUr example now looks like:

7       1      1
5       2      2
5c     3      3
5a     4      4
4      5       5
4a    7       7
1      2a     2
3      4a     4
2a    5a     5
2      5c     5

now that everything is a numerical value, in cell D1 type =RANK.EQ(C1,C$1#,1)  and it'll rank all the cells in Columns C according to their position in the array.  Spreadsheet now looks like:

7       1      1      1
5       2      2      2
5c     3      3      4
5a     4      4      5
4      5       5      7
4a    7       7      10
1      2a     2      2
3      4a     4      5
2a    5a     5      7
2      5c     5      7

Now that everything is ranked, in Cell E1 type =SORTBY(B1#,D1:D10,1) and it will bring the values from column "B" over, but rank them by the values in column "D".  You will now have:

7       1      1      1      1
5       2      2      2      2
5c     3      3      4      2a
5a     4      4      5      3
4      5       5      7      4
4a    7       7      10    4a
1      2a     2      2      5
3      4a     4      5      5a
2a    5a     5      7      5c
2      5c     5      7      7


Highlight and Hide columns B,C, and D and you have your list sorted.  Obviously this is a little clunky for 10 values, but if you have several hundred rooms, it may be worth it.  it's also a pain to add more rooms after the sort.  It's not super handy, but that's how you do it.

[WLJ]

Have you tried dropping the spreadsheet into something like LibreOffice and see how it handles it?

[Brad Johnson]

Have you tried dropping the spreadsheet into something like LibreOffice and see how it handles it?

I haven't, but it's not a big enough deal to spend a lot of time on. I can live with it as-is, but was hoping one of you reprobates had a point & click solution.

Brad

[230RN]

Is this similar to dogmush's solution?

How would it work if you went to four numerical digits with the LSD (right-hand digit) being 0-9 with 0 corresponding to no separate little sub-room, and 1-9 corresponding to A-I little sub-rooms?  Am I understanding the problem correctly?

You can parse out that last letter if required later.
Divide room designation by 10, subtract the integer value from the full decimal value... if 0, no subrooms, if ,1 subroom A, if .2, B, etc.

[MechAg94]

OK, my first suggestion doesn't work.

Adding a new column for the letter designator and doing a custom two-tiered sort as suggest by Mech works.... sort of.

When I did it I got..

120A
120B
120C
120

221A
221B
221

I can't figure out how to get the numeric only room to list first, followed by the alpha numerics.

You might try using a symbol in the second column that has no letter.  I don't know if any symbols sort higher than "A".  There ought to be something that does.