Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting (Excel 2000 )

    Hi Guru's,

    Hopefully you can help me........If I have one column with address for example 10 lyons St.......
    how do I sort the contents by street name, not including number. The number keeps interfering.

    Thanks Kindly,

    Kerrie <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Re: Sorting (Excel 2000 )

    I think you're going to have to parse the column of data. Also, what happens when you have 1 Lyons Street and 2 Lyons Street? How do you want them sorted?

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting (Excel 2000 )

    You will either need to put the number and street name in separate columns, or create an additional column (possibly hidden) that contains the street name without the number. The latter can be done using a formula if you can gurantee that every street adderss will have a number. If not, you will need to do it manually, or write a User Defined Function.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Sorting (Excel 2000 )

    Kerrie,

    This solution will depend on how consistent your data is. I have allowed for some cells to have street numbers and some not, and also I have tried to cater for inconsistent blanks in the data.

    1. Add 2 columns to the right of your address column.

    2. In the first column to the right enter the formula "=TRIM(A5)" where the address is in A5, and without the apostrophes. This will get rid of unwanted blanks in the address, leaving one blank between words.

    3. In the second column enter the formula:

    "=IF(LEFT(B5,1)<"A",RIGHT(B5,LEN(B5)-FIND(" ",B5)),B5)"

    without the apostrophes. This checks if the first character has a lower value than "A" (a number) , and if so removes the whole number, otherwise it copies the whole cell.

    You should now be able to sort on the right hand column entered.

    I hope this helps.

    Good Luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •