Sorting house numbers like 9, 9A, 9B, 10 in Excel

2

2

I have Excel files which contain addresses with house numbers like 9 9A 9B 10. I'd like to sort these, but if I format the cells as text, I get the incorrect ordering 10 9 9A 9B, and if I format them as numbers, I get the incorrect ordering 9 10 9A 9B.

Is there a way to get these sorted correctly without having to transform the house numbers into something like 0009 0009 A 0009 B 0010 in an additional column? (And if that is the only solution, how do I do the transformation?)

(Btw, this similar question didn't really help: sort numbers and numbers+text together in Excel)

Just Browsing

Posted 2016-11-02T12:42:56.477

Reputation: 21

So adding a space between 9 and A will not be a desired solution? – duDE – 2016-11-02T13:12:21.757

1

Possible duplicate of sort numbers and numbers+text together in Excel

– Ƭᴇcʜιᴇ007 – 2016-11-02T13:47:26.453

1IMO it's the exact same question, which has multiple valid solutions provided already so I've voted this as a duplicate. If you're not satisfied with the existing answers, consider earning a bit of rep by helping others, and then placing a bounty on the existing one to garner new answers. Unless there's a specific problem you're having implementing what's offered there (as "it didn't really help" doesn't tell us anything about why or what you've tried)? – Ƭᴇcʜιᴇ007 – 2016-11-02T13:51:39.620

@Ƭᴇcʜιᴇ007 Well, in the other question the numbers are all 3-digit, here you have 9 10 which means alphabetical sort doesn't give the correct results. The first answer explains how Excel sorts under different circumstances, but none offers the result asked for in this question, and the second answer gives a result where 10 comes before 9. – m69 ''snarky and unwelcoming'' – 2016-11-02T22:54:20.247

@duDE That doesn't give the required ordering: formatted as numbers, 9 b comes after all other numbers (and typing 9 a weirdly creates 0.375); formatted as text, 10comes before 9, or 9 a and 9 b come after all other numbers, depending on selected sort options. – m69 ''snarky and unwelcoming'' – 2016-11-02T23:09:35.427

Answers

1

One way to do this is to use hidden columns for sorting. Yeah, specifically not what you wanted, but it's idiomatic for Excel.

I needed to sort table rows by chapter + page + row for a subset of a document.

I encoded the Chapters 7, 7A, 8 as 10, 20, 30.

The page numbers restarted at the beginning of each chapter, so those stayed the same.

The rows on each page I encoded as 1,2,3.

The columns don't need to be hidden, obviously. It looks cleaner though.

I used regular expressions to transform the input.

I used cygwin64 and bash, sed, awk to manipulate the data.

The problem you're describing is a classic and recurring one. Know that the effort you're putting into your solution will pay dividends your entire career.

Lyle S.

Posted 2016-11-02T12:42:56.477

Reputation: 131

It's strange that no one at Microsoft has thought it necessary to provide an easy solution to such an obvious real-world problem as sorting addresses. – m69 ''snarky and unwelcoming'' – 2016-11-02T22:58:23.713

1Can you be a little more explicit as to how to actually accomplish what was asked in the question? – fixer1234 – 2016-11-03T03:58:13.730

Asked: 2016-11-02T12:42:56.477

Viewed: 423 times

Active: 2016-11-02T14:11:42.480