Main Forums > The Roundtable

excel sorting, mix of letters/numbers

(1/3) > >>

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.

Navigation

[0] Message Index

[#] Next page