Sorting postcodes in Access/Excel?

  Roadgiant 15:11 28 Jun 2004

I wonder if anyone can help me, I have set up a database for customers and one of the things I am trying to do is sort by Postcode.

If for example I have the postcodes M1,M2,M15, and M23 and try to sort the list by Postcode order it produces M1,M15,M2,M23 instead of M1,M2,M15,M23 as required.

The database is set up in Access but Excel produces exactly the same result, hopefully someone can help me, thanks in advance

  GroupFC 15:41 28 Jun 2004

I think it is something to do with the formatting of the cells. I have just tried it in excel and the only way I could get it to work was to put the "m" in a seperate cell and then sort by the numbers. A bit of a pain if the data has already been entered!

Hopefully one of the excel experts will come along soon and put us both right!

  stlucia 16:06 28 Jun 2004

Numbering in this sequence is normal computer logic -- it looks at the first digit first, then the next if there is one.

I'm not aware of an Exel function to sort it the way you want, but you can automatically set up another couple of columns to hold the first character (the 'M' for instance) and the remaining characters (the numbers bit) separately. Do this using the LEFT worksheet function for the first character, and the MID worksheet function to get the remaining characters starting with the second one. Then you can sort the whole worksheet using these two new columns, as suggested by GroupFC.

  pj123 17:15 28 Jun 2004

In Excel highlight the column holding the postcodes. Go to Format, Cells,

  pj123 17:17 28 Jun 2004

Sorry, hit the wrong button. Start again.

In Excel highlight the column holding the postcodes. Go to Format, Cells, select the Number Tab, then Special, then Zip Code. OK that and do another sort.

  GroupFC 17:26 28 Jun 2004

Are you sure that will do it? I tried that (admittedly In Excel 97!) but it didn't seem to give the desired result.

I've just tried it with a random selection of postcodes and M34 for example comes after M3 rather than after M24, where it should have been in my small sample - which is exactly the result Roadgiant got!

  pj123 17:35 28 Jun 2004

GroupFC, yes you're right. I just read the thread again. Needs to be sorted the human way. Scrap that and I will do a bit more work on it.

  VoG II 17:57 28 Jun 2004
  pj123 17:24 29 Jun 2004

Been working on it for some time until I saw the link from God (sorry, I mean VoG™). Seems to solve the problem so I won't continue. I was using the same system as Mark W from Texas. (putting zero's in front).

  end 18:00 29 Jun 2004

"saw the link from God....."......

so THAT"s where God has " vanished to" , is it?????? if he is that busy on this forum dealing with all your queries, NO WONDER I"M find ding it rather attrociouslsy difficult, if not , at times, well nigh, impossible, to get in touch with him...........

and I "read" "Mark W" as Mark Twain, and thought " that"s novel"..............

  AccessMoron 17:28 04 Jul 2004

in the query builder add the folowing line to the SQL.

ORDER BY Left(PostCodeCol,1),

{this will order bt the first letter}

Right(postcodeCol, len(postcodecol)-1)

{this will remove the first letter and order by the number}

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment utiliser Live Photos ?