| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I list postcodes in order in Excel?
Now that is VERY clever, thanks a lot Roger
Sheila "Roger Govier" wrote: Hi Sheila & Jilly Make a copy of your data first - just in case!!! Assuming that you only want to sort by the first part of the UK postcode, then set up a helper column and use this formula. This assumes your postcodes are in column A, change reference to suit. =IF(ISNUMBER(--MID(A1,2,1)),LEFT(A1,1)&TEXT(MID(A1,2,2),"00"),LEF T(A1,2)&TEXT(MID(A1,3,2),"00")) Copy down the column as far as required, select ALL OF YOUR DATA, and sort by the helper column ascending. Regards Roger Govier Sheila D wrote: This is exactly the question I came on line to look for an answer to so hope it's all right to tag on the end of this one - my postcodes have varying alpha, numeric i.e SE2, SE11, S2 etc and cannot see how to get them to sort properly as there is no logical way to split alpha / numeric. Thanks for any help Sheila "Dave O" wrote: Please excuse my ignorance: are post codes always in the format alpha-alpha-number-number-number? Excel is treating these entries as text because of the BA characters. If you separate the alpha characters from the numerics into two different columns, you can sort on the different columns to get your desired results. So if you enter two new columns, you can parse the alpha characters into one column using this formula (assuming that post codes always have two leading alpha characters, which was the genesis behind my original question): =MID(A1,1,2) .... where A1 is a post code. Then get the numeric portion in a new column with this formula: =MID(A1,3,LEN(A1)) If a post code has a varying number of leading alpha characters, let us know and we'll try a different solution. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Address List in Excel | Excel Discussion (Misc queries) | |||
| CREATE LIST OBJECT IN EXCEL | Excel Worksheet Functions | |||
| Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
| list of all excel 2000functions | Excel Discussion (Misc queries) | |||
| Excel List to Table | Excel Discussion (Misc queries) | |||
Threaded Mode
